1

I have few large tables and I need to join them. In SQL it looks like:

select * from dbo.Table1 t1 join dbo.Table1 t1Parent on t1.ParentId = t1Parent.Id join dbo.MappingT1T3 t2 on t1Parent.Id = t2.ExternalId and t2.[Type] = 1 join dbo.Table3 t3 on t2.ForeignId = t3 .Id where t1.[Type] = 3

Tried to convert this query to a such LINQ:

from t1 in dbo.Table1 join t1Parent in dbo.Table1 on t1.ParentId equals t1Parent.Id join t2 in dbo.MappingT1T3 on new { Id = t1Parent.Id, [Type] = (int)1 } equals new { Id = t2.ExternalId, [Type] = (int)t2.[Type] } join t3 in dbo.Table3 on t2.ForeignId equals t3.Id where t1.[Type] == 3;

But seems execution plan differs a lot. Profile says that it tries to load all tables without conditions..

Improve this question

1 Answer 1

Reset to default
1

Try putting the constant to a seperate condition...

from t1 in dbo.Table1 where t1.[Type] == 3 // <--- PUT THIS ONE HIGHER join t1Parent in dbo.Table1 on t1.ParentId equals t1Parent.Id join t2 in dbo.MappingT1T3 on t1Parent.Id equals Id = t2.ExternalId where (int)t2.[Type] == 1 // <--- SEPARATE CONDITION join t3 in dbo.Table3 on t2.ForeignId equals t3.Id;
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.