Unable to create a constant value of type 'Anonymous type'. Only primitive types or enumeration types are supported in this context

.net c# entity-framework linq linq-to-sql

Question

I'm incredibly new tolinq as well as entity foundation. I'm attempting to figure out why the following isn't functioning. The error that was made is "A constant value of type "Anonymous type" could not be created. In this situation, only primitive types or enumeration types are supported."

Despite my numerous attempts, I continue to receive a primitive types-related issue. I would be very grateful if someone could look at the code below and, perhaps, identify the problem.

        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

In order to avoid doing the cross context joins in EF, I've changed my strategy and created a view. I was hoping you could assist me with the following.

ObjectQuery.ToTraceString() gives me correct SQL that retrieves records from the database, however the resLst in EntityFramework always returns 0. Is there a clear explanation for why this is occurring?

  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
9
8/2/2013 4:06:48 PM

Popular Answer

The call to ToList() on details is the issue. In EF, an IEnumerable can only be referenced within a Query if it is of a basic type (e. g. int). You CAN, however, make reference to another IQueryable. Therefore, removing the ToList() call should enable this.

EDIT: In a similar vein, you should remove the call to ToList() on ctx.CompanySettings.

This will have the added benefit of running just one query rather than two. EF will produce the following if you remove the ToList() from details:

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: In order to do this action across contexts, you must first load the query into memory (for example, by calling AsEnumerable()) and perform the necessary joins there. Consider using Contains if the joins serve as filters and it's crucial for these to occur in SQL (). For instance

var serials = details.Select(d => d.Serial);
var filtered = RepCtxt.RegisteredBikes.Where(r => details.Contains(r.Serial);
7
8/2/2013 10:26:19 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow