Unable to create a constant value of type 'Ano

问题:

I am extremely new to linq and entity framework. I am trying to resolve a problem as to why the below isn't working. The error produced is "Unable to create a constant value of type 'Anonymous type'. Only primitive types or enumeration types are supported in this context."

I've tried this so many different ways yet still get an error relating to primitive types. I would greatly appreciate it if someone could take a look at the below code and hopefully point out where it's going wrong.

        public Entities.BikeData[] GetBikesWithExpiredSyncDeadline(int noOfDays) {

        using (OfficeEntities cxt = GetContext()) 
        using (ReportingEntities RepCxt = GetReportingContext()) {
            Data.Repository.Abstract.IBikeRepository BikeRepos = new Data.Repository.Concrete.BikeRepository();                

            var details = (from sd in cxt.BikeDetails
                                        where sd.autoreminder == true
                                            && (sd.lastremindersent == null || sd.lastremindersent < EntityFunctions.AddDays(DateTime.UtcNow, noOfDays * -1))
                                            && (sd.emailaddress != null && sd.emailaddress.Trim() != "")
                                        select new {
                                            Serial = sd.Serial,
                                            EmailAddress = sd.emailaddress
                                        }).ToList();

            var resLst = (from r in RepCxt.RegisteredBikes
                          join d in details on r.Serial equals d.Serial 
                          join cs in cxt.CompanySettings.ToList() on r.CompanyID equals cs.CompanyID
                          where (!r.lastupdate.HasValue || r.lastupdate < EntityFunctions.AddDays(DateTime.UtcNow, cs.AutoNotificationFrequency * -1))
                          select new Entities.BikeData {
                              ID = r.ID,
                              Name = r.Ship,
                              Serial = r.Serial,
                              LastUpdate = r.lastupdate,
                              DaysSinceLastSync = (r.lastupdate.HasValue? EntityFunctions.DiffDays(r.lastupdate.Value, DateTime.UtcNow).Value : -1),
                              EmailAddress = (d.EmailAddress == null ? string.Empty : (String.IsNullOrEmpty(d.EmailAddress) ? r.ShipEmailAddress : d.EmailAddress))
                          });

            return resLst.ToArray();
        }
    }

UPDATE

I've taken a different approach with this now by creating a view so I no longer need to do the cross context joins in EF. I was hoping you may be able to help with the below.

When I run objectQuery.ToTraceString() it provides me with valid SQL that returns records in the db, however the resLst in EntityFramework always comes back with 0. Is there anything obvious as to why this is happening?

  var resLst = (from ls in cxt.BikeLastUpdates
                          where (!ls.lastupdate.HasValue || ls.lastupdate < EntityFunctions.AddDays(DateTime.UtcNow, ls.AutoNotificationFrequency * -1))
                          && (ls.autoreminder ==true)
                          && (ls.lastremindersent == null || ls.lastremindersent < EntityFunctions.AddDays(DateTime.UtcNow, 3 * -1))
                          && (ls.emailaddress !=null && ls.emailaddress.Trim() != "")
                          select new Entities.BikeData{
                              ID = (ls.ID ?? new Guid()),
                              Name = ls.Bike,
                              Serial = ls.Serial,
                              LastUpdate = ls.lastupdate,
                              EmailAddress = (String.IsNullOrEmpty(ls.emailaddress) ?  ls.ShipEmailAddress : ls.emailaddress)
                          });

            var objectQuery = resLst as ObjectQuery;

            return resLst.ToArray();

回答1:

The problem is the ToList() call on details. In EF, you can only refer to an IEnumerable inside a Query if that IEnumerable is of a simple type (e. g. int). However, you CAN refer to another IQueryable. Thus, dropping the ToList() call should make this work.

EDIT: similarly, you should drop the ToList() call on ctx.CompanySettings.

This will have the added advantage of executing only 1 query instead of 2. If you drop the ToList() on details, EF will generate something like:

SELECT ...
FROM RegisteredBikes rb
JOIN (
    /* this is your "details" IQueryable */
    SELECT Serial, EmailAddress
    FROM BikeDetails
    WHERE ...
) bd
    ON rb.Serial = b.Serial
JOIN CompanySettings cs
    ON ...
WHERE ...

EDIT: to do this across contexts, you'll need to bring the query into memory (e. g. by calling AsEnumerable() and do the relevant joins there. If the joins act as filters and it's important for these to happen in SQL, consider using Contains(). For example

var serials = details.Select(d => d.Serial);
var filtered = RepCtxt.RegisteredBikes.Where(r => details.Contains(r.Serial);