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();
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);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With