How do I write an Entity Framework 4 query for the following SQL?

Question

I have an Entity Framework 4 model in my application. I need it to generate the following SQL:

SELECT *
FROM       Reads            AS R
INNER JOIN Images           AS I ON R.ReadId = I.ReadId AND I.ImageTypeId = 2
LEFT OUTER JOIN Alarms      AS A ON R.ReadId = A.ReadId AND A.DomainId IN (103,102,101,2,1,12) AND A.i_active = 1
LEFT OUTER JOIN ListDetails AS L ON L.ListDetailId = A.ListDetailId
WHERE R.i_active = 1

Here's what I have now for this query in my code:

var items = from read  in query
            join plate in context.Images      on read.ReadId        equals plate.ReadId
            join temp1 in context.Alarms      on read.ReadId        equals temp1.ReadId       into alarms  from alarm in  alarms.DefaultIfEmpty()
            join temp2 in context.ListDetails on alarm.ListDetailId equals temp2.ListDetailId into entries from entry in entries.DefaultIfEmpty()
            where plate.ImageTypeId == 2 && plate.IActive == 1
            select new { read, plate, alarm, entry.OfficerNotes };

How do I modify the Entity Framework query to get the SQL query I need?

Aducci · Accepted Answer · 2012-07-20 14:38:27Z

2

For Left Joins - I suggest using a from with a where instead of join, I find it makes it cleaner

For Inner Joins with multiple join conditions - you need to join on two matching anonymous types, or use a from with where conditions

For x IN list, you need to specify your list outside the query and use the Any method or Contains method

List<int> domainIds = new List<int>() { 103,102,101,2,1,12 };

    var items = from read  in query
                join plate in context.Images
                on new { read.ReadId, ImageTypeId = 2 } equals new { plate.ReadId, plate.ImageTypeId }
                from alarm in context.Alarms
                                     .Where(x => x.IActive == 1)
                                     .Where(x => domainIds.Any(y => y == x.DomainId))
                                     .Where(x => read.ReadId == x.ReadId)
                                     .DefaultIfEmpty()
                from entry in context.ListDetails 
                                     .Where(x => alarm.ListDetailId == x.ListDetailId)
                                     .DefaultIfEmpty()
                select new { read, plate, alarm, entry.OfficerNotes };

answered Jul 20, 2012 at 14:38

Aducci

26.8k8 gold badges67 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tony Vitabile Over a year ago

Thank you! I made those changes to the code and they work perfectly! However, I did use Contains on the domain ID test.

Ruslan Over a year ago

I would recommend to use Contains instead of Any. Contains produces clean IN statement, while Any produces awful EXISTS (SELECT 1 AS [C1] FROM (... SELECT 103 AS [C1] FROM (SELECT 1 AS X) AS [SingleRowTable1] UNION ALL SELECT 102 AS [C1] FROM (SELECT 1 AS X) AS [SingleRowTable2]) AS [UnionAll1] WHERE [UnionAll1].[C1] = [Extent3].[DomainId])

Aducci Over a year ago

@Ruslan - In this case, you are probably correct - Contains is better. I like using Any, because it allows for more complex conditions

Collectives™ on Stack Overflow

How do I write an Entity Framework 4 query for the following SQL?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related