Linq query using DbContext not working as expected.

gilesrpa · January 2017

Hi, this is my first post, so please bear with me if this appears to be a simple problem.

I have just converted a medium complexity stored procedure into the Linq expression. As you will see from the code, the stored procedure uses many unions.


DbContext Entities = new DbContext();
int[] SourceID = { 8888, 9999 };
int docId = 1;
try
{
var docList = ( 
from st in Entities.Table1
join sa in Entities.Table2
on st.SourceFileId equals sa.SourceFileId
join sfs in Entities.Table3
on sa.SourceFileStatusId equals sfs.SourceFileStatusId
join sf in Entities.Table4
on st.SourceFileId equals sf.SourceFileId into x
from y in x.DefaultIfEmpty()
join sfl in Entities.Table5
on y.SourceFolderId equals sfl.SourceFolderId into d
from r in d.DefaultIfEmpty()
where st.DocId == docId &&
SourceID.Contains(sfs.SourceFileStatusId)
group new { sfs, r, sa } by new { sfs.SourceFileStatusId, sfs.WebText, st.SourceFileId, r.SourceSystemName, sa.AuditLogDateTime }
into grp
orderby grp.Min(g => g.sa.AuditLogDateTime)
select new ReturnDataType
{
Status = "XXXXXXXXXXXXXXXXXX : " + grp.Key.SourceSystemName + "<br/> SourceFileID:" + grp.Key.SourceFileId,
dateTime = grp.Min(g => g.sa.AuditLogDateTime)
}
).Concat
(
from st1 in Entities.Table1
join bsf1 in Entities.Table6
on st1.SourceFileId equals bsf1.SourceFileId into x
from a in x.DefaultIfEmpty()
join eb1 in Entities.Table7
on a.BatchId equals eb1.BatchId into y
from b in y.DefaultIfEmpty()
join ba1 in Entities.Table8
on b.BatchId equals ba1.BatchId into z
from c in z.DefaultIfEmpty()
where st1.DocId == docId &&
b.BatchId != 0
group new { b } by new { b.BatchId, b.BatchDate }
into grp
select new ReturnDataType
{
Status = "XXXXXXXXXXXXXXXXXX : " + grp.Key.BatchId.ToString(),
dateTime = grp.Key.BatchDate
}
).Concat
(
from st2 in Entities.Table1
join bsf2 in Entities.Table6
on st2.SourceFileId equals bsf2.SourceFileId into x
from a in x.DefaultIfEmpty()
join eb2 in Entities.Table7
on a.BatchId equals eb2.BatchId into y
from b in y.DefaultIfEmpty()
join ba2 in Entities.Table8
on b.BatchId equals ba2.BatchId into z
from c in z.DefaultIfEmpty()
join dc2 in Entities.Table9
on st2.DocId equals dc2.DocId
where st2.DocId == docId &&
b.BatchId > 0 &&
dc2.DocId > 0 &&
c.AuditText.Contains("Print Partner Transmission Finished")
group new { c } by new { c.AuditDateTime }
into grp
select new ReturnDataType
{
Status = "XXXXXXXXXXXXXXXXXX : ",
dateTime = grp.Key.AuditDateTime
}
).Concat
(
from dc3 in Entities.Table9
join d3 in Entities.Table10
on dc3.DestinationId equals d3.DestinationId
join ar3 in Entities.Table11
on dc3.AccountRuleId equals ar3.AccountRuleId
where dc3.DocId == docId &&
d3.SendToFtp == true &&
dc3.DateProcessed != null
select new ReturnDataType
{
Status = d3.WebTextName == null ? "Unknown" : "XXXXXXXXXXXXXXXXXX : " + d3.WebTextName,
dateTime = dc3.DateProcessed
}
).Concat
(from dc4 in Entities.Table9
join d4 in Entities.Table10
on dc4.DestinationId equals d4.DestinationId
join ar4 in Entities.Table11
on dc4.AccountRuleId equals ar4.AccountRuleId
join eh4 in Entities.Table12
on dc4.EmailId equals eh4.EmailId into x
from e in x.DefaultIfEmpty()
where dc4.DocId == docId &&
d4.SendToEmail == true &&
e.EmailId > 0
select new ReturnDataType
{
Status = "XXXXXXXXXXXXXXXXXX : " + d4.EmailToAddress,
dateTime = dc4.DateProcessed
}
).Concat
(from dc5 in Entities.Table9
join d5 in Entities.Table10
on dc5.DestinationId equals d5.DestinationId
join ar5 in Entities.Table11
on dc5.AccountRuleId equals ar5.AccountRuleId
join eh5 in Entities.Table12
on dc5.EmailId equals eh5.EmailId into x
from e in x.DefaultIfEmpty()
where dc5.DocId == docId &&
d5.SendToEmail == true &&
e.EmailId > 0 &&
dc5.DateProcessed != null
select new ReturnDataType
{
Status = "XXXXXXXXXXXXXXXXXX : " + e.ToAddress + "<br/>" + "EmailID: " + e.EmailId,
dateTime = e.DateProcessed
}
);
}
catch (Exception ex)
{

Console.WriteLine(ex.ToString());

}

My problem is this. If I use the follow code within Linqpad and press the Execute(F5) button, I get a message at the bottom stating that the query was successful and how long it took. But if I try and step through the code using either F10 or F11, I get an System.NullReferenceException error. If I click on the more details option on the error alert, i am then told that an object reference is not set. That's it, nothing to explain which object is having the problem.

If I take that same code, but this time remove the Entities object and use the table names as listed on the left instead in the connection manager, then the query works.

Also the object ReturnDataType has two properties. Status, which is defined as a string and dateTime which is defined as a nullable DateTime.

When I try and set the value dateTime in the select statements, I get an error stating:


'DateTime' does not contain a definition for 'GetValueOrDefault' and no extension method 'GetValueOrDefault' accepting a first argument of type 'DateTime' could be found

Any help on this would be gratefully accepted.

JoeAlbahari · January 2017

When you get the NullReferenceException, what sort of error dialog do you see? Is there an option to report it?

If you want to include an identifier such as 'Entities' before all your entity sets, the best way to do it is this:

var entities = this;
...
     var docList = (
        from st in entities.Table1...

On an unrelated but important point, your query is a transliteration of SQL into LINQ. This makes it far more complex than it needs to be, and horrible to maintain.

Can I suggest you read this article, in particular the sections on associations and shaping data:
http://www.linqpad.net/WhyLINQBeatsSQL.aspx

If you follow the advice on associations and shaping, you should rarely need to join - and rarely need the DefaultIfEmpty() operator.

Linq query using DbContext not working as expected.

Comments