Unable to convert SQL Query to LINQ Query for Left Outer Join

Question

Problem Statement:

I'm trying to convert one of my Sql to linq query, but I'm unable to get the desired output which i need. Can anyone suggest me what i should do?

SQL Query:

SELECT AssetTagging.AssetID, AssetTagging.AssetDescription, [Return].RequestStatus
FROM  AssetTagging
LEFT OUTER JOIN [Return] ON AssetTagging.AssetID = [Return].AssetID
LEFT OUTER JOIN Issue ON AssetTagging.AssetID = Issue.AssetID
WHERE (Issue.AssetID IS NULL) OR ([Return].RequestStatus = 'Approved')

Linq Query I'm using:

var result = (from at in db.AssetTagging.AsEnumerable()
                  join r in db.Return on at.AssetID equals r.AssetID
                  orderby at.AssetID
                  where !db.Issue.Any(issue=>issue.AssetID==at.AssetID) || r.RequestStatus=="Approved"
                  select new globalTestModel
                  {
                    model1=at
                  }).ToList();


//I know that in Linq query I'm using Inner join instead of Left Join,but i'm getting error if i use left join instead of inner join?

What am I doing wrong??

Any suggestion to get desired query like Sql in Linq?

Asset Tag table:

Issue table:

Return table:

Desired Output :

Answer 1

You need to remove .AsEnumerable(), because you want your query to be translated to sql. Right now it would be using linq-to-objects and if you are using a left join with linq-to-object you need to check for null reference exceptions. rt could be null, so rt.RequestStatus would throw an exception.

*I believe rt should be r in your example

You can't project to an existing entity, so you need to change your select to:

select new PocoClass
{
  model1=at 
}

//New class definition
public PocoClass
{
  public AssetTagging model1 { get; set; }
}

Answer 2

Try the following I am assuming that you still want the cases where r is null unless r is not null and request status = approved.

You have to check to verify r!=null before checking the request status and you will still need to include when r is null to get the complete result set. I haven't tested this, but this should put you in the right direction.

Good luck.

  var result = (from at in db.AssetTagging
               join r in db.Return.DefaultIfEmpty()
               on at.AssetID equals r.AssetID
               join i in db.Issue.DefaultIfEmpty()
               on  at.AssetID equals i.AssetID
               where 
               (r == null || (r!=null && r.RequestStatus == "Approved"))
               || i == null
               select new {
                at.AssetID,
                at.AssetDescription,
                IssueID = (i!=null) ? i.IssueID : null),
                ReturnID = (r!=null) ? r.ReturnID: null),
                ReturnStatus = (r!=null) 
                     ? r.ReturnStatus 
                     : null}).ToList();

Answer 3

You need to do like this:

 var result = from at in db.AssetTagging
              join r in db.Returns on at.AssetID equals r.AssetID into a
              from returns into a.DefaultIfEmpty()
              join i in db.Issues on at.AssetID equals I.AssetID into b
              from issues into b.DefaultIfEmpty()
              where issues.AssetID != null || returns.RequestStatus == "Approved"
              select new
                    {
                      AssetID = at.AssetID,
                      AssetDescription = at.AssetDescription,
                      Status = returns != null ? returns.RequestStatus : null
                    }.ToList();

Answer 4

try like following:

from at in db.AssetTagging
join r in db.Return on at.AssetID equals r.AssetID into res1
from atr in res1.DefaultIfEmpty()
join  i in db.Issues on i.AssetID==at.AssetID into res2
from obj in res2.DefaultIfEmpty()
select at
where i.AssetID == null || r.RequestStatus equals "Approved"

Just make two times left outer join and then do filter on where condition.

Also have first look at this msdn article about left outer join using linq.

Answer 5

I know this isn't exactly what you've asked for, but it might be useful anyway.

If you have access to the database to execute SQL queries, I would suggest creating a view. You can then drop the view into your DBML file the same way as you would with a table, and have much simpler Linq expressions in your C# code.

CREATE VIEW [Asset_Issue_Return_Joined] AS
SELECT AssetTagging.AssetID, AssetTagging.AssetDescription, [Return].RequestStatus
FROM  AssetTagging
LEFT OUTER JOIN [Return] ON AssetTagging.AssetID = [Return].AssetID
LEFT OUTER JOIN Issue ON AssetTagging.AssetID = Issue.AssetID
WHERE (Issue.AssetID IS NULL) OR ([Return].RequestStatus = 'Approved')

Answer 6

Here is the complete query

  var result = (from assetTagging in db.AssetTagging
                join return0 in db.Return on assetTagging.AssetID equals return0.AssetID into returns
                from return0 in returns.DefaultIfEmpty()
                join issue in db.Issue on assetTagging.AssetID equals issue.AssetID into issues
                from issue in issues.DefaultIfEmpty()
                where issue.AssetID == null || return0.RequestStatus == "Approved"
                select new
                {
                   assetTagging.AssetID,
                   assetTagging.AssetDescription,
                   return0.RequestStatus
                }).ToList();