CODEWITHSUNDEEP
c#linqentity-framework-4.1
Allison Steranko
Allison Steranko

Reputation: 337

Specified type member not supported in LINQ to Entities

I'm trying to do a group by of insurance policy payments that are split between the main policy and then a sub policy (policy.ID, policy.POL2 below). Ideally I want the last 3 payments, the due date, paid date, amount, and a suspense amount regardless of whether the payment was split or not.

If the payment was split, I need to sum the amount paid to the main policy and subpolicy, but the other three fields should be identical, so I don't want duplicate records.

I thought something like this would work, but I'm getting a

The specified type member 'PAYDUE' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

because PAYDUE is a DateTime. As you see, I tried to use the builtin EntityFunctions method, but still get the same error

var mc = new ModelContext();
var policy = mc.Polmasts.Find("N400603");
var payments = from p in mc.Paymnts
               where p.POLICY == policy.ID ||
                     p.POLICY == policy.POL2
               orderby p.PAYPD_ descending
               group p by p.PAYPD_
               into g
               select new 
                       {
                           payduedate = EntityFunctions.TruncateTime(g.FirstOrDefault().PAYDUE),
                           paypaiddate = EntityFunctions.TruncateTime(g.FirstOrDefault().PAYPD),
                           paymentamount = g.Sum(a=>a.AMOUNT),
                           paysuspense = g.FirstOrDefault().SUSP
                       };
foreach (var payment in payments)
{
    Console.WriteLine("%=========== \n PAY DUE DATE: {0} \n PAYMENT DATE: {1} \n AMOUNT: {2} \n SUSPENSE: {3}", payment.payduedate, payment.paypaiddate, payment.paymentamount, payment.paysuspense);
}

Upvotes: 3

Views: 2121

Answers (1)

Rob H
Rob H

Reputation: 1849

Take a look at this question. I'm guessing that PAYDUE is not a part of the table schema?

You might try separating out the final select in order to make it materialize the table before performing the select. Something like this (I haven't tested it, though!)

var paymentsRaw = (from p in mc.Paymnts
               where p.POLICY == policy.ID ||
                     p.POLICY == policy.POL2
               orderby p.PAYPD_ descending
               group p by p.PAYPD_).ToList();

var payments = from g in paymentsRaw 
               select new 
                       {
                           payduedate = EntityFunctions.TruncateTime(g.FirstOrDefault().PAYDUE),
                           paypaiddate = EntityFunctions.TruncateTime(g.FirstOrDefault().PAYPD),
                           paymentamount = g.Sum(a=>a.AMOUNT),
                           paysuspense = g.FirstOrDefault().SUSP
                       };

Upvotes: 3

Related Questions