Join problem

[Trixz]

I have problems with joins and left outer joins

Code: Select all

from l in header.Orderlines
join t in context.ItemType on l.itemId equals t.Id
select new { l.id, l.desc, t.name }

This will result in a first select where all itemType is selected, and then the orderlines query.

I was expecting a join in one sql. Our program is running really slow since some joined tables have 100000 of posts.

If i instead do

Code: Select all

... new { l.id, l.desc, l.ItemType.name }

a new sql is run for each Row!!! Can't have a long list of sql that runs.
This example may be fixed by specifying that the refered table should be loaded at same time (loadoption), but if the link is multiple "steps" away, I still get ugly performance.

I also tried to do a storeprocedure like this

Code: Select all

... new {l.id, l.desc, context.GetItemTypeName(l.id) }

This will also generate a select to get all the lines, and then, for each line, call the getItemTypeName function.


Running 5.25.39, can't run a later version since our long-running-storeprocedures will timeout.

[AndreyR]

The behaviour you describe is relevant to the situation when LINQ to Objects is used instead of LINQ to SQL.
One of the objects you use in the join operation was already materialized (or queried) and it already
represents an object collection, that's why the join is performed in memory after the
second object is also materialized.
I would recommend you to put both object collections into one DataContext
and perform the join in it, this should solve the problem.