Linq help - Sql trace returns result but datacontext returns null
var adminCov = db.SearchAgg_AdminCovs.SingleOrDefault(l => l.AdminCovGuid == covSourceGuid);
adminCov keeps returning zero. When I run the SQL profiler I can see the generated linq, when I go past this in Management Studio I get the expected result.
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 the result. What am I missing here? Thanks for any help, ~ ck in San Diego
source to share
This is a really good question. I had the same issue with Linq to SQL when selecting invoices in a date range. Some of them were not present in the object results until they were included in the generated SQL query result. I had serious problems with this because some of the invoices were not exported to the accounting software.
I did to create a stored procedure and everything was fine.
I would really like to know the true solution for this and why it happened.
source to share
Do you get your result if you change your operator as follows (notice "Equals" instead of "==")?
var adminCov = db.SearchAgg_AdminCovs.SingleOrDefault(l => l.AdminCovGuid.Equals(covSourceGuid));
I have run into some of the equality issues with GUIDs in the past (usually in Unit Testing), but the same could apply here.
source to share
Using Single or SingleOrDefault is always risky if there can be zero or more than one record that meets the criteria. SingleOrDefault returns null if there is no match, or more than one match (in your case, it could be more than one, since you say there is data). This should result in "Single" throwing an exception if you try it. Alternatively, you can try using FirstOrDefault to get the first match if there is at least one match. It will return null if there is no match.
source to share