Linq help - Sql trace returns result, but datacontext returning null

Question

var adminCov = db.SearchAgg_AdminCovs.SingleOrDefault(l => l.AdminCovGuid == covSourceGuid);

adminCov keeps coming back null. When I run SQL profiler, I can see the generated linq, when I past that into management Studio, I get the result I expect.

LinqToSql generates this:

exec sp_executesql N'SELECT [t0].[AdminCovGuid], [t0].[AdminPolicyId], [t0].[CertSerialNumber], [t0].[CertNumber], [t0].[PseudoInsurerCd], [t0].[SourceSystemCode], [t0].[CovSeqNumber], [t0].[RiderSeqNumber], [t0].[CovRiderIndicator], [t0].[CovCd], [t0].[AddrSeqNumber], [t0].[TransferSeqNumber], [t0].[CovStatusIndicator], [t0].[CovEffectiveDate], [t0].[CovExpirationDate], [t0].[CovCancelDate], [t0].[ClmIntegCode], [t0].[ClmNumber], [t0].[ClmCertSeqNumber], [t0].[TermNumber], [t0].[CovPaidThruDate], [t0].[BillThruDate], [t0].[BillModeCode], [t0].[BillModeDesc], [t0].[CalcModeCode], [t0].[CalcModeDesc], [t0].[Form1Name], [t0].[BenefitAmt], [t0].[CovDesc], [t0].[ProdLineDesc], [t0].[PremiumAmt], [t0].[PremiumTypeIndicator], [t0].[PremiumTypeDesc]
FROM [dbo].[SearchAgg_AdminCov] AS [t0]
WHERE [t0].[AdminCovGuid] = @p0',N'@p0 uniqueidentifier',@p0='D2689692-33E8-4B31-A77B-2D3A627145D4'

When I execute, I get a result. What am I missing here? Thanks for any help, ~ck in San Diego

Answer 1

This is really good question. I had the same issue with Linq to SQL when selecting invoices in the date range. Some of them were not present in the object results while they were included in the generated SQL query result. I had some serious trouble with it because some invoices were not exported to the accounting software.

What I did was to create stored procedure and everything worked perfectly fine.

I would really like to know the true solution for this and why it happened.

Answer 2

Do you get your result back if you change your statement, as follows (note the "Equals" instead of the "==")?

var adminCov = db.SearchAgg_AdminCovs.SingleOrDefault(l => l.AdminCovGuid.Equals(covSourceGuid));

I have run into some comparison equality issues with GUIDs in the past (usually in Unit Testing), but the same might apply here.

Answer 3

Using Single or SingleOrDefault is always risky if there could be zero or more than one record matching the criteria. SingleOrDefault will return null if there are no matches or more than one match (in your case it could be more than one since you say there is data). This should cause a "Single" to throw an exception, if you try that. As an alternative you could try to use FirstOrDefault to get the first match if there is at least one match. It will return null when there are no matches.

Answer 4

Does your SearchAgg_AdminCovs table has a primary key set? I'm not sure, but I used have some headache about forget to set one, but not sure if it was select/update/insert or delete.