CODEWITHSUNDEEP
sqllinq-to-sql
Brian Salta
Brian Salta

Reputation: 1576

SQL to Linq conversion left outer join

I am trying to convert this SQL statement into LINQ

SELECT     
  a.UserId, b.Date, a.Value1
FROM         
  web.Table2 AS b
LEFT OUTER JOIN
  web.Table1 AS a 
ON 
  b.Date = a.CreateDate AND UserId = 1

This is what I have that is not working:

Dim query = From a In ctx.Table1 _
        Group Join b In ctx.Table2 On b.Date Equals a.CreateDate Into group1 = Group
        From x In group1.DefaultIfEmpty() _
        Where ls.UserId = 1 _
        Select a.UserId, x.Date, a.Value1

Upvotes: 0

Views: 116

Answers (2)

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171401

You could always directly execute your query in LINQ To SQL, e.g.:

IEnumerable<MyClass> results = db.ExecuteQuery<MyClass>(@"
    SELECT a.UserId, b.Date, a.Value1 
    FROM web.Table2 AS b 
    LEFT OUTER JOIN web.Table1 AS a ON b.Date = a.CreateDate 
        AND UserId = 1");

Upvotes: 0

rs.
rs.

Reputation: 27427

Your code should be (check alias b, you were using x)

Dim query = From a In ctx.Table1 _
        Join b In ctx.Table2 _ 
        On b.Date Equals a.CreateDate Into group1 = Group _
        Where a.UserId = 1 _
        From b In group1.DefaultIfEmpty() _            
        Select a.UserId, b.Date, a.Value1

Upvotes: 1

Related Questions