Simple T-SQL to LINQ

Question

0.00/5 (No votes)

See more:

I have a simple SQL statement as below:

Select s.StaffID, s.FirstName + ' ' + s.LastName as FullName from Staff s 
left outer join CheckIn c on c.StaffID = s.StaffID
WHERE s.ContractStatusID=1 and c.CheckInID is null

The SQL statement works fine, now I want to write a LINQ query for it, I tried this but it is not the right way:

var db = new SREntities();
            var all = from s in db.Staffs
                      join c in db.CheckIns on s.StaffID equals c.StaffID into ps
                      from c in ps.DefaultIfEmpty()
                      where s.ContractStatusID == 1  && c.CheckInID == null
                      select new { s.StaffID, FullName = s.FirstName + " " + s.LastName};

Can you please help me write the above LINQ query, I simply want to retrieve all records from Staff table who don't have any record in CheckIn table.

Thanks in advance

What I have tried:

var db = new SREntities();
            var all = from s in db.Staffs
                      join c in db.CheckIns on s.StaffID equals c.StaffID into ps
                      from c in ps.DefaultIfEmpty()
                      where s.ContractStatusID == 1  && c.CheckInID == null
                      select new { s.StaffID, FullName = s.FirstName + " " + s.LastName};

Posted 2-Jan-17 20:12pm

Husain Ahmad Khalid

Updated 3-Jan-17 19:11pm

Add a Solution

Comments

Maciej Los 3-Jan-17 2:26am

What is wrong with your Linq version of Sql statement?

Husain Ahmad Khalid 3-Jan-17 2:44am

it is not giving the required result, it doesn't return any value

Maciej Los 3-Jan-17 2:46am

Can't you use Except? I mean all from Staff table except that people who's checked in ;)

Husain Ahmad Khalid 3-Jan-17 2:59am

let's give it a try

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2017-01-02T22:46:00

Method #1

As i mentioned in the comment to the question you need to use Except method[^]

C#

var db = new SREntities();
var allCheckedIn = from s in db.Staffs
        join c in db.CheckIns on s.StaffID equals c.StaffID
        select s;

var NotCheckedInOnly = (from s in db.Staffs).Except(allCheckedIn);

For further details, please see:
Supported and Unsupported LINQ Methods (LINQ to Entities)[^]
NOT IN clause for Entities[^]

Method #2

Another way to achieve that is to use Where + Any:

var result = db.Staffs.Where(s=>db.CheckIns.Any(c=>c.StaffID!=s.StaffID));

Try!
Good luck!

Jon McKee · Accepted Answer · 2017-01-03T16:47:00

There's nothing wrong with Maciej's response but I think explaining what went wrong in your example might help in the future.

Your LINQ example is very close - here's the proper query:

C#

var query = from staff in db.Staffs
            join checkIn in db.CheckIns on staff.StaffID equals checkIn.StaffID 
              into result
            from r in result.DefaultIfEmpty()
            where staff.ContractStatusID == 1 && (r == null || r.CheckIn == null)
            select new 
            { 
              staff.StaffID, 
              FullName = staff.FirstName + " " + staff.LastName 
            };

When doing a group join the results list always exists even if empty. Normally these empty results are skipped when iterating over the result set. When you use DefaultIfEmpty() you are setting these empty lists to their default in order to always have a pairing with the left table (left outer join). The rows for db.CheckIns are reference types though so their default is null. This means that where ... c.CheckIn == null is causing the problem since you cannot access null.CheckIn.

To handle both cases where A) the results are empty and B) the CheckIn value is null, you need to do a short-circuit check on c == null. This will catch the case where the result list is empty before you attempt to access the CheckIn property to check if that is null.

EDIT: To better explain group join, it works like below where each row in the left table is associated with one of these:

C#

//Results returned
resultList = new List<T>(); resultList.Add(result1); resultList.Add(result2); //etc...
//No results returned
resultList = null;
//DefaultIfEmpty
resultList = new resultList<T>(); resultList.Add(default(T));

Simple T-SQL to LINQ

2 solutions

Solution 1

Method #1

Method #2

Solution 2

Add your solution here

Preview 0