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