5 Replies - 110 Views - Last Post: 11 February 2020 - 10:56 AM Rate Topic: -----

#1 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6833
  • View blog
  • Posts: 28,342
  • Joined: 12-December 12

LINQ checking for empty responses

Posted 11 February 2020 - 06:33 AM

I had a LINQ query which was, essentially, gathering some records and then Sum-ming Quantity * UnitCost. I received an error

Quote

System.InvalidOperationException: 'The cast to value type 'System.Double' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type.'


I believe this is because the initial resultset is empty, rather than a specific value (Quantity or UnitCost) being null; neither are declared as nullable.

So I am trying to split the query into more steps; to gather the records, check if there "are any", then Sum the products or default to 0.

                DateTime start = startDate.Date;
                DateTime stop = stopDate.Date;

                double total = 0;

                var fromResponses = (from jobs in Context.PMJobs.AsNoTracking()
                           join responses in Context.PMResponses.AsNoTracking() on jobs.UID equals responses.JobID
                           let compareDate = DbFunctions.TruncateTime(jobs.StartDate)
                           where jobs.CostCode == costCode && jobs.Cancelled == false && 
                           (compareDate >= start && compareDate <= stop)
                           select responses);

                var totalFromResponses = (fromResponses == null || !fromResponses.Any()) ? 0 : fromResponses.Sum(x => x.Quantity * (double)x.UnitCost);

                total += totalFromResponses;

Why am I having to jump through such hoops with Any() ? Surely there must be an easier way to resolve this? I tried with DefaultIfEmpty() but this is the same issue because I'm still left with checking if the IQueryable response is empty.

Is This A Good Question/Topic? 0
  • +

Replies To: LINQ checking for empty responses

#2 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6833
  • View blog
  • Posts: 28,342
  • Joined: 12-December 12

Re: LINQ checking for empty responses

Posted 11 February 2020 - 07:12 AM

Well I suppose this is better, using ToList() to, essentially, break out of LINQ to Entities to LINQ to Objects (I believe):

                var totalFromResponses = (from jobs in Context.PMJobs.AsNoTracking()
                           join responses in Context.PMResponses.AsNoTracking() on jobs.UID equals responses.JobID
                           let compareDate = DbFunctions.TruncateTime(jobs.StartDate)
                           where jobs.CostCode == costCode && jobs.Cancelled == false && 
                           (compareDate >= start && compareDate <= stop)
                           select responses)?.ToList()?.Sum(x => x.Quantity * (double)x.UnitCost) ?? 0;

                total += totalFromResponses;

Was This Post Helpful? 0
  • +
  • -

#3 Skydiver   User is offline

  • Code herder
  • member icon

Reputation: 7239
  • View blog
  • Posts: 24,542
  • Joined: 05-May 12

Re: LINQ checking for empty responses

Posted 11 February 2020 - 08:16 AM

This is a complete aside, but when dealing with money, you should be using decimal, not double. :)
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6833
  • View blog
  • Posts: 28,342
  • Joined: 12-December 12

Re: LINQ checking for empty responses

Posted 11 February 2020 - 08:21 AM

Hello. Yes, I know ;)

The database is extremely poor and inconsistent but we have to work with it "as is" (until some time in the distant future).
Was This Post Helpful? 0
  • +
  • -

#5 Skydiver   User is offline

  • Code herder
  • member icon

Reputation: 7239
  • View blog
  • Posts: 24,542
  • Joined: 05-May 12

Re: LINQ checking for empty responses

Posted 11 February 2020 - 09:29 AM

I'm away from a machine where I do play with VS right now, but can you do something like:
var totalFromResponses =
    (
        from jobs in Context.PMJobs.AsNoTracking()
        join responses in Context.PMResponses.AsNoTracking()
        on jobs.UID equals responses.JobID
        let compareDate = DbFunctions.TruncateTime(jobs.StartDate)
        where jobs.CostCode == costCode && 
              jobs.Cancelled == false && 
              compareDate >= start &&
              compareDate <= stop
        select responses.Quantity * (decimal) responses.UnitCost
    )
    ?.Sum() ?? 0;

total += totalFromResponses;



OR

var totalFromResponses =
    (
        from jobs in Context.PMJobs.AsNoTracking()
        join responses in Context.PMResponses.AsNoTracking()
        on jobs.UID equals responses.JobID
        let compareDate = DbFunctions.TruncateTime(jobs.StartDate)
        where jobs.CostCode == costCode && 
              jobs.Cancelled == false && 
              compareDate >= start &&
              compareDate <= stop
        select responses.Quantity * (decimal) responses.UnitCost
    )
    ?.AsEnumerable()
    ?.Sum() ?? 0;

total += totalFromResponses;


Was This Post Helpful? 1
  • +
  • -

#6 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6833
  • View blog
  • Posts: 28,342
  • Joined: 12-December 12

Re: LINQ checking for empty responses

Posted 11 February 2020 - 10:56 AM

In pub;) I'll give those a bash tomorrow.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1