CODEWITHSUNDEEP
c#sqllambda
nick gowdy
nick gowdy

Reputation: 6511

How do I translate this SQL query to Lambda or LINQ that uses (where is null)

I've been trying to modify some rows of data in SQL to test in my application and I've noticed my query in Lambda brings back 0 rows when I am expecting 2387 row. The source of the problem is I am using parenthesis in a WHERE clause in SQL to look at some null values. This is the SQL query:

SQL

-- THIS WORKS!
select * from vwAppsWithIssues
   where fld1stCheckAllocatedTo = 'nicholasg' and fldStage = 1
   and (fldStopStartDate is null or fldStopEndDate is not null)

-- The query was originally this (doesn't return rows)
  select * from vwAppsWithIssues
  where fld1stCheckAllocatedTo = 'nicholasg' and fldStage = 1
  and (fldStopStartDate = null or fldStopEndDate <> null)

LAMBDA query that returns 0 rows

public static int GetApplicationsFirstCount(string UserId)
        {
            try
            {
                using (IME_CheckOffEntities IME_CheckOffEntities = new IME_CheckOffEntities())
                {
                    return IME_CheckOffEntities.vwAppsWithIssues
                         .Where(a => a.fld1stCheckAllocatedTo == UserId && a.fldStage == 1 && (a.fldStopStartDate == null || a.fldStopEndDate != null))
                         .ToList().Count;
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }

Update

Using LINQPad I have written this expression:

VwAppsWithIssues
.Where (v => v.Fld1stCheckAllocatedTo == "nicholasg"
&& v.FldStage == 1 
&& (v.FldStopStartDate == null || v.FldStopEndDate != null)).Count()

that generates this sql

SELECT COUNT(*) AS [value]
FROM [vwAppsWithIssues] AS [t0]
WHERE ([t0].[fld1stCheckAllocatedTo] = @p0) AND ([t0].[fldStage] = @p1) AND (([t0].[fldStopStartDate] IS NULL) OR ([t0].[fldStopEndDate] IS NOT NULL))

So now that I have some lambda that I think will work, I simply copy it to visual studio.

  var count = IME_CheckOffEntities.vwAppsWithIssues
      .Where(v => v.fld1stCheckAllocatedTo == "nicholasg" && v.fldStage == 1 && (v.fldStopStartDate == null || v.fldStopEndDate != null)).Count();

It still returns only 0 rows?! I am passing in the right userId in C# as well.

My count in c# also returns 0 rows. Any idea how I can rewrite this C# query?

Upvotes: 1

Views: 227

Answers (1)

tschmit007
tschmit007

Reputation: 7800

from linqpad, on one of my schema

from
    f in Files
where
    f.PubDate == null || f.FilingDate != null
select
    f.IdFile

is translated as follow

SELECT 
[Extent1].[idFichier] AS [idFichier]
FROM [dbo].[tableF] AS [Extent1]
WHERE ([Extent1].[datePubliF] IS NULL) OR ([Extent1].[dateDepotF] IS NOT NULL)

so, in your case, are you, for example, sure of the UserId value ?

Upvotes: 2

Related Questions