How do I load an entity collection based on the results of a view?
Joined: 10-Aug-2012
If you use a PrefetchPath it would be hard, this is why: When you use a prefetch, the results are built in two phases:
-
Fetch the main collection (Employee) SELECT * FROM Employee
-
Fetch the subpath collection SELECT * FROM EmployeeSubordinate es WHERE EmployeeId IN (SELECT * FROM Employee)
-
The two collection are merged in memory so they match the relation defined. In this case you would end up with a Empoyee collection like:
Bob --- Emma Emma --- James --- Sam James Sam
Since you already have a view that returns all deep subordinates of a given employee, you could do:
- Fetch the main employee you are looking for
var theBoss = new EmployeeEntity(1);
using (var adapter = new DataAccessAdapter())
{
adapter.FetchEntity(employee);
}
- Fetch the subordinate collection. Assuming your view has those columns: BossId, BossName, SubordinateId, SubornidateName
var flatEmployees = new FlatEmployeesHiearchyTypedView ();
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
var bucket = new RelationPredicateBucket(FlatEmployeesHiearchyFields.BossId == 1);
adapter.FetchTypedView(flatEmployees , bucket, true);
}
- Add the subornidates to the root employee. Note, below line assumes you have a m:n navigator on Employee side named Employee.Subordinates, that connect: Employee - EmployeeSubordinate - Employee.
theBoss.Subordinates.AddRange(bossSubordinates);
- Does that make sense to you?
- What LLBLGen version are you using right now?
BTW, Welcome back! 
Joined: 10-Aug-2012
GenericTypeTea wrote:
EntityFramework will autoconnect up the object graph for any related entity. I.e. if I loaded the Boss and loaded all the Subordinates via joining to my view then the Boss entity would be like the following:
- Bob - Emma - James - SamI.e. The Boss's employees will still only have Emma in it, but Emma herself would have James and Sam.
However in LLBLGen, it doesn't seem to be this clever (no offence intended) when it comes to building object graphs. This isn't really a problem though, because as you've said already, I can just load the main employee with the prefetch and then load the related employees with a separate collection load with a relation to that view.
It's a feature we're looking into for v4, which has 'datascope's which might offer this feature. EF has a central context which can easily do this, we don't so it's not really doable in v3.x. In general you don't really want this feature to be enabled by default I think, because it can make associations between entities without you realizing it, which might keep entities into memory longer than you might think.
It seems to me that on second run of a query (first call is slow due to model/cache building I guess?) LLBLGen takes about an eighth of the time of Entity Framework. By the way, we don't deal with Employee objects at all, but it's good for simplicity.
The initial 200ms you see is mainly due to the physical db connection being made. If you switch off connection pooling in the connection string, you'll see it's slower each time. there are other initial data structure initializations too, but not as much as with e.g. NH or EF.
- It takes EntityFramework 300ms to load a particular object. It takes LLBLGen 30-50ms (200ms first time).
- Loading every object in question with all it's related data (there's a lot) from the database takes EntityFramework 19860ms. LLBLGen takes 4480ms (didn't try it twice, but I guess it'll get quicker on the next run.
Can you link me to a post to do with how the queries are generated/cached to explain the performance? Also, have you done any EF4 (Code First) vs LLBLGen performance metrics?
LLBLGen Pro doesn't cache anything. 
The philosophy behind it is that the central application state in the DB is what should be accessed whenever possible, not a cache at data-access level. If you need caching for performance reasons, try to cache as high up the call chain as possible, so caching at the data-access level isn't really useful in that light either.
the performance drop in many EF queries comes due to the fact their query system is seriously broken: it generates massive queries which are horribly slow. Especially when you use .Include() or inheritance hierarchies.
We haven't done any performance metrics between EF code first and our own framework, as it's not really useful for others: a performance benchmark on machine X with model Y and query Z might make framework A look better than framework B, but unless there's a technical explanation because that is, it's a moot comparison. So we profile our own code and try to optimize whatever bottleneck we run into. That way we're as fast as we can make it, in all situations.
Code first is really a mapping system, it has the same characteristics at runtime as when you'd use database first with EF. The only difference is that it tries to create a DB at startup, or tries to modify a schema if you let it. Of course, maintaining a lot of poco classes and migration code in C# is something that will cause a problem later on, sadly when the developers who made the system have moved on to other projects. You see, with code first, there's no model overview. You have code, but which code is for the DB and which isn't, can be unclear. If your project goes to several versions, which is common, you'll have migration upon migration code in C#. What that will do to the schema in the DB is unclear. To me, I always have found it ironic that people who find DB first is totally wrong, want to do code first, which is actually the same thing, you just start on the other side of the mapping. IMHO the best way is to start with the model and then create code + DB and mappings from that as the model is the base for both. Starting with either code or tables is starting from the projection result of the model. DB first or Code first require a reverse engineering of the model from the db schema or code classes and project it to the other side.