Linq query using DbContext not working as expected.

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.


Comments

  • 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.
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!