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.

Olympic

I noticed today that calling a DateTime constructor e.g. new DateTime(2015, 1, 1) will throw this error. Cryptic! – Eohippus

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.

Tully

This will probalby fail as you have a GroupBy without specifying an aggregate. This will either fail or return all underlyin objects in IGroupings – Sewole

Table will contain 500k rows - after grouping 20 rows. – Olympic

In that case it should perform fine since the grouping will happen on the server where it's efficient, and working with 20 rows in memory will be very quick. – Tully

the sole reason to never use EF – Eloiseelon

Answer 2

Message is clear : linq to entities doesn't support objects without a parameterless ctor.

So

Solution1

enumerate before (or use an intermediate anonymous type and enumerate on that one)

.ToList()
.Select(x => new FundedCount(
                    x.Key.BuyerId,
                    x.Count() / 30 * daysInMonth))
                .ToList();

Solution2

add a parameterless ctor to your FundedCount class (if it's possible)

public FundedCount() {}

and use

.Select(x => new FundedCount{
                        <Property1> = x.Key.BuyerId,
                        <Property2> = x.Count() / 30 * daysInMonth
                         })
                    .ToList();

Mopboard

This Solution2 is the best imo, since there is no intermediate ToList() and therefore no intermediate SQL execution either. Thanks! – Grampus

Answer 3

It's complaining because it can't convert references to FundedCount to SQL statements.

All LINQ providers convert LINQ statements and expressions to operations that their target can understand. LINQ to SQL and LINQ to EF will convert LINQ to SQL, PLINQ will convert it to Tasks and parallel operations, LINQ to Sharepoint will convert it to CAML etc.

What happens if they can't do the conversion, depends on the provider. Some providers will return intermediate results and convert the rest of the query to a LINQ to Objects query. Others will simply fail with an error message.

Failing with a message is actually a better option when talking to a database. Otherwise the server would have to return all columns to the client when only 1 or 2 would be actually necessary.

In your case you should modify your select to return an anonymous type with the data you want, call ToList() and THEN create the FundedCount objects, eg:

.Select( x=> new {Id=x.Key.BuyerId,Count=x.Count()/30 * daysInMonth)
.ToList()
.Select(y => new FundedCount(y.Id,y.Count))
.ToList();

The first ToList() will force the generation of the SQL statement and execute the query that will return only the data you need. The rest of the query is actually Linq to Objects and will get the data and create the final objects

Sewole

Answer 4

I had the same exception in GroupBy. I found that the exception "Only parameterless constructors and initializers are supported in LINQ to Entities" is not 100% accurate description.

I had a GroupBy() in my "Linq to EntityFramework query" which used a struct as a Key in GroupBy. That did not work. When I changed that struct to normal class everything worked fine.

Code sample

var affectedRegistrationsGrouped = await db.Registrations
  .Include(r => r.Person)
  .Where(r =>
      //whatever
  )
  .GroupBy(r => new GroupByKey
  {
      EventId = r.EventId, 
      SportId = r.SportId.Value
  })
 .ToListAsync();

...
...
// this does not work
private struct GroupByKey() {...}

// this works fine
private class GroupByKey() {...}

Prisca

Ha, it chokes on the struct keyword. Belated thanks! – Crusade