How do I convert SQL Query to Lambda?

Question

I have the following SQL query that returns the results I need:

SELECT 
       STAFF_ID
FROM [dbo].[StaffTable]
WHERE STAFF_ID NOT IN (SELECT STAFF_ID 
                                     FROM [dbo].[StaffingTable]
                                     WHERE [DATE] = @DATE
                                     AND MODEL_ID = @Model)

I have the following controller method to try and return the correct results:

public JsonResult GetStaffResults(DateTime date, string modelId)
        {
            Guid modelGuid = Guid.Parse(modelId);
            var settings = new JsonSerializerSettings();

            var staff = context.StaffTable.Select(c => new
            {
                Id = c.StaffId,
                Name = c.StaffName
            });

            var staffing = context.StaffingTable.Select(c => new
            {
                modelId = c.ModelId,
                manufacturerId = c.ManufacturerId,
                staffId = c.StaffId,
                date = c.Date,
                recordId = c.RecordId
            });

            var staffResults = staff.Where(p => staffing.Select(o => o.modelId).First() == modelGuid && !staffing.Select(o => o.date).Contains(date));

            return Json(shiftResults, settings);
        }

However, I'm struggling with the Lambda expression, it returns no results so I've missed something somewhere.

Answer 1

You can try something like this:

 public JsonResult GetStaffResults(DateTime date, string modelId)
        {
            Guid modelGuid = Guid.Parse(modelId);
            var settings = new JsonSerializerSettings();

            var staffQuery = context.StaffTable
                .Where(s => !context
                    .StaffingTable
                    .Any(st => st.StaffId = s.StaffId && st.modelId == modelGuid && st.date == date))
                .Select(c => new
                {
                    Id = c.StaffId,
                    Name = c.StaffName
                });

            return Json(staffQuery.ToList(), settings);
        }

Answer 2

You are doing separate query with Select which has a performance issue as the queries will return IEnumerable<T> objects. If you want to have separate queries then try to get the query results as IQueryable<T> which will construct the queries and will load data at the end.

For example:

var query =    
    from st in context.StaffTable  
    where !(from stff in context.StaffingTable  
            select stff.CustomerID)
            where stff.MODEL_ID = ModelIdVariable AND stff.DATE = DATEVariable
           .Contains(st.CustomerID)    
    select st;

Declare and populate the ModelIdVariable and DATEVariable variables before the query.

Then you can do query.ToList<T>() where you need the data to be loaded.