Only parameterless constructors and initializers are supported in LINQ to Entities message

Question

I have a method that returns data from an EF model.

I'm getting the above message, but I can't wotk our how to circumvent the problem.

    public static IEnumerable<FundedCount> GetFundedCount()
    {
        var today = DateTime.Now;
        var daysInMonth = DateTime.DaysInMonth(today.Year, today.Month);

        var day1 = DateTime.Now.AddDays(-1);
        var day31 = DateTime.Now.AddDays(-31);

        using (var uow = new UnitOfWork(ConnectionString.PaydayLenders))
        {
            var r = new Repository<MatchHistory>(uow.Context);

            return r.Find()
                .Where(x =>
                    x.AppliedOn >= day1 && x.AppliedOn <= day31 &&
                    x.ResultTypeId == (int)MatchResultType.Accepted)
                .GroupBy(x => new { x.BuyerId, x.AppliedOn })
                .Select(x => new FundedCount(
                    x.Key.BuyerId,
                    x.Count() / 30 * daysInMonth))
                .ToList();
        }
    }

FundedCount is not an EF enity, MatchHistory is, so can't understand why it is complaining.

All advice appreciated.

Answer 1

The reason it is complaining is because it doesn't know how to translate your Select() into a SQL expression. If you need to do a data transformation to a POCO that is not an entity, you should first get the relevant data from EF and then transform it to the POCO.

In your case it should be as simple as calling ToList() earlier:

return r.Find()
        .Where(x => x.AppliedOn >= day1 && x.AppliedOn <= day31 &&
                    x.ResultTypeId == (int)MatchResultType.Accepted)
        .GroupBy(x => new { x.BuyerId, x.AppliedOn })
        .ToList() // this causes the query to execute
        .Select(x => new FundedCount(x.Key.BuyerId, x.Count() / 30 * daysInMonth));

Be careful with this, though, and make sure that you're limiting the size of the data set returned by ToList() as much as possible so that you're not trying to load an entire table into memory.