0

I have a Linq Query in my application that looks like

claim[] claimLst = (from clms in dbContext.claims where 
                                 clms.@switch == 8
                            && (clms.Status == "Received Ok" || clms.Auto_Resubmit == 1)
                            && ((short)clms.tiMarkedForSubmission_M == 0 
                               || (short)clms.tiMarkedForSubmission_T == 0)
                            select clms).ToArray();

When I look at it in SQL Server Profiler I can see that it gets resolved to 

exec sp_executesql N'SELECT [t0].[id], [t0].[datetime_received], 
[t0].[Uniqueid], [t0].[Practice_number], [t0].[regnum], [t0].[claim_number], 
[t0].[Status], 
[t0].[Date_time], [t0].[Action], [t0].[Med_resp], [t0].[Tar_resp], [t0].[switch],
[t0].[Auto_Resubmit], [t0].[Auto_Resubmit_Count], 
[t0].[tiMarkedForSubmission_M], [t0].[tiMarkedForSubmission_T]
FROM [dbo].[claims] AS [t0]
WHERE ([t0].[switch] = @p0) AND (([t0].[Status] = @p1) OR ([t0].[Auto_Resubmit] = @p2)) 
AND ((([t0].[tiMarkedForSubmission_M]) = @p3)
OR (([t0].[tiMarkedForSubmission_T]) = @p4))'
,N'@p0 int,@p1 varchar(8000),@p2 int,@p3 int,@p4 int',
@p0=8,@p1='Received Ok',@p2=1,@p3=0,@p4=0

When I run this sql, I get no results. Where as when I run the query as 

Select * from dbo.claims clms where 
                  clms.switch = 8 AND 
                 (clms.Status = 'Received Ok' 
                  OR clms.Auto_Resubmit = 1)
                  AND (clms.tiMarkedForSubmission_M = 0 
                 OR clms.tiMarkedForSubmission_T = 0)

I get 10 records.

What is different in the two queries that 1 gives me results and the other does not?

Anand
  • 316
  • 1
  • 14
  • Well if you look at the results, do they appear to match the query? Have you tried removing individual parts of your where clause to work out what's causing the problem? – Jon Skeet Jul 30 '15 at 08:09
  • What are the type of `tiMarkedForSubmission_M` and `tiMarkedForSubmission_M` ? – Hadi Mohammadi Jul 30 '15 at 08:11
  • why do you have to explicitly cast `clms.tiMarkedForSubmission_M` and `clms.tiMarkedForSubmission_T` to `short` ? – Nilesh Jul 30 '15 at 08:14
  • @Hadi Mohammadi when I pulled the table into the Linq To SQL file, the table generated tiMarkedForSubmission_M and tiMarkedForSubmission_T to Byte. So I just type casted it to short. – Anand Jul 30 '15 at 08:27
  • 1
    @Anand the query is case sensitive in linq, is your record have to same case? – User2012384 Jul 30 '15 at 08:31
  • @jonSkeet. Yes the results do match the Query. No, I have not tried removing individual parts. – Anand Jul 30 '15 at 08:32
  • 1
    @User2012384 thanks, I think the issue was "Received Ok" was actually stored as "Received ok" O => o. So I changed the query to claim[] claimLst = (from clms in dbContext.claims where clms.@switch == 8 && (clms.Status.ToLower == "received ok" || clms.Auto_Resubmit == 1) && ((short)clms.tiMarkedForSubmission_M == 0 || (short)clms.tiMarkedForSubmission_T == 0) select clms).ToArray(); – Anand Jul 31 '15 at 07:36
  • @Anand Try not to use "ToLower", it's not a good practice, try use this : http://stackoverflow.com/questions/15784340/linq-function-like-net-string-compareordinal – User2012384 Jul 31 '15 at 07:56
  • 1
    @User2012384 Thanks again. I will definitely use String.Compare function with OrdinalIgnoreCase option. – Anand Aug 02 '15 at 07:11

0 Answers0