Convert special SQL query to Linq

Question

do you know how write this SQL Query to linq ?

SELECT * 
FROM 
  a 

  INNER JOIN b 
  ON a.FkSubmissionId = b.Id 

  RIGHT JOIN c 
  ON a.FkItemId = c.Id 

WHERE 
 (b.FkUserId = '...' OR b.FkUserId is null) 
 and 
 (c.FkTenderId = 2)

I use Linquer and the best I have from the tool is that :

Linq :

from 
  items in _context.Items 

from 
  si in _context.si

  join s in _context.s 
  on new { fki = si.fki } equals new { fki = s.Id } 

into 
  submissions_join

from 
  s in submissions_join.DefaultIfEmpty()

...

Result in SQL :

SELECT * 
FROM 
  [Items] AS [t0]

  CROSS JOIN [SubmissionsItems] AS [t1]

  LEFT OUTER JOIN [Submissions] AS [t2] 
  ON [t1].[FkSubmissionId] = [t2].[Id]

WHERE 
  (([t2].[FkUserId] = @p0) OR (([t2].[FkUserId]) IS NULL)) 
  AND 
  ([t0].[FkTenderId] = @p1)

So the final result it not what I get from the query I need...

Thank you for your help !!!

take a look @ http://stackoverflow.com/questions/695506/linq-left-join-group-by-and-count to get how to do a left-join. — TheHe, Sep 17 '12 at 05:44

score 1 · Accepted Answer · answered Sep 17 '12 at 13:44

Try this:

var part1 =
    from x in a
    join y in b on x.FkSubmissionId equals y.Id
    where b.FkUserId = "..."
    select new {x, y};

var part2 =
    from c in z
    where c.FkTenderId == 2
    join xy in part1
        on z.Id equals xy.x.FkItemId
        into xys
    from xy in xys.DefaultIfEmpty()
    select new {xy.x, xy.y, z};

score 0 · Answer 2 · answered Sep 17 '12 at 14:03

0

I would reorder your query so you can use a left join instead of a right join

var query = from c in context.C
            from a in context.A.Where(x => c.Id == x.FkItemId)
                               .DefaultIfEmpty()
            join b in context.B on a.FkSubmissionId equals b.id
            where b.FkUserId == '...' || b.FkUserId == null
            where c.FkTenderId == 2
            select new {
              a,
              b,
              c
            };

answered Sep 17 '12 at 14:03

Aducci

26,101
8
63
67

That looks like it might result in n+1 queries. What does the SQL look like? – Thom Smith Sep 17 '12 at 14:39
@ThomSmith - What part of my query makes you think it will result in n+1 queries? – Aducci Sep 17 '12 at 14:55
The second line. Rather than using `Join`, it's running a different `Where` on `A` for each `c`. The query framework may or may not optimize this to a SQL join. – Thom Smith Sep 17 '12 at 15:12
1

@ThomSmith - It will be a left join. You can rewrite it to use a join, into, and from, but I feel it is less readable. – Aducci Sep 17 '12 at 15:16

Convert special SQL query to Linq

2 Answers2