Eager / lazy loading problem

I've made few tests with both eager and lazy load (build from end of March) and the generated SQL code is almost same (running with my typed UOW in LinqPad). Something could be badly set up or my understanding of how lightspeed works is not correct.

Test case: join 2 tables (Status.CaptionGuid and CodeItem.CodeItemGuid) on primary and foreign key and get some results back, association 1 to 1.

Test 1: LINQ

var statuses = (from s in Statuses from ci in CodeItems where s.CaptionGuid == ci.Id orderby s.StatusRef select new { Id = s.Id, StatusRef = s.StatusRef, StatusOrder = s.StatusOrder, CaptionText = ci.Caption }).ToList();

This works great regardless eager/lazy load setup. It generated the SQL join

SELECT t0.StatusGuid AS [t0.StatusGuid], t0.StatusRef AS [t0.StatusRef], t0.StatusOrder AS [t0.StatusOrder], t1.Caption AS [t1.Caption] FROM Status t0 INNER JOIN CodeItem t1 ON t0.CaptionGuid = t1.CodeItemGUID WHERE (t0.CaptionGuid = t1.CodeItemGUID) ORDER BY t0.StatusRef

Test 2: LINQ and accessing eager loaded table via property

Generated code: [EagerLoad] [ForeignKeyField("CaptionGuid")] [ReverseAssociation("Status")] private readonly EntityHolder _codeItem = new EntityHolder();

Linq: var statuses = (from s in Statuses where s.CodeItem != null orderby s.StatusRef select new { Id = s.Id, StatusRef = s.StatusRef, StatusOrder = s.StatusOrder, CaptionText = s.CodeItem.CodeItemRef }).ToList();

This one is heavy and not efficient (N+1) query:

SELECT Status.StatusGuid AS [Status.StatusGuid], ... FROM Status WHERE (Status.CaptionGuid IS NOT NULL) ORDER BY Status.StatusRef

Then for each row:

SELECT CodeItem.CodeItemGUID AS [CodeItem.CodeItemGUID], ... FROM CodeItem WHERE (CodeItem.CodeItemGUID = 'e72d5e38-a92f-4e20-9970-cd4eb9367fa2')

SELECT CodeItem.CodeItemGUID AS [CodeItem.CodeItemGUID], ... FROM CodeItem WHERE (CodeItem.CodeItemGUID = '0467800c-be99-44b9-821a-237ca9615169')

... (and same for all rows)

Also, even I explicitly name columns, all columns are returned by the query. I'm not sure if it's a performance gain when the table has 50 columns or so (yes, already normalized). Lets assume that tables are huge and it's not possible to lazy load the whole table. What I would write directly in SQL is something like....

SELECT t0.StatusGuid AS [t0.StatusGuid], t0.StatusRef AS [t0.StatusRef], t0.StatusOrder AS [t0.StatusOrder], t1.Caption AS [t1.Caption] FROM Status t0 INNER JOIN CodeItem t1 ON t0.CaptionGuid = t1.CodeItemGUID WHERE (t0.CaptionGuid in ('e72d5e38-a92f-4e20-9970-cd4eb9367fa2','0467800c-be99-44b9-821a-237ca9615169',...)) ORDER BY t0.StatusRef

Is is possible to generate such query from LightSpeed? What do you suggest and what is the preferred way?

Thanks! Bob

This occurs because you're using a projection that spans multiple tables. Such a projection is currently resolved on the client rather than on the server. Unfortunately we have an issue at the moment that means you are not seeing eager loading in this case because there is also a projection in play (I believe the idea is to avoid loading other entities that might not be needed for the projection, but the behaviour is unexpected and in this case harmful).

The solution is to perform the join explicitly using a LINQ join:

var contribInfos = from c in UnitOfWork.Contributions
                   join ct in UnitOfWork.ContentTypes on c.ContentTypeId equals ct.Id
                   where c.ContentType != null
                   select new { Id = c.Id, ContentTypeDesc = ct.Description };

// Generated SQL:
SELECT
  t0.Id AS [t0.Id],
  t1.Description AS [t1.Description]
FROM
  Contributions t0
INNER JOIN
  ContentTypes t1
ON
  t0.ContentTypeId = t1.Id
WHERE
  t0.ContentTypeId IS NOT NULL

This avoids both the client-side projection and the N+1 problem.