Unable to create a constant value of type 'Anonymous type'. Only primitive types or enumeration types are supported in this context
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);
JIbber4568
Updated on July 25, 2022Comments
-
JIbber4568 almost 2 years
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();
-
JIbber4568 over 10 yearsThanks for that. The reason it's done like that is we are having to join cross context. As far as I'm aware without the .ToList() we are unable to query cross context. Is there a different approach with this I should maybe be taking to allow cross context joins?
-
ChaseMedallion over 10 years@JIbber4568 in order to join cross-context, you'd need to pull the entire company settings table into memory and filter using a WHERE IN () (Contains in EF).
-
JIbber4568 over 10 yearsThanks for your help with this. I've taken a slightly different approach with this but am now having problems with Entity Framework inheriting the results from the DB. Is there anything obvious in the above update that may be causing this? Thanks