L2S System.DateTime has no supported translation to SQL-CodePudding

My Data Service is has method of type IQueryable and my controller is trying to convert the date time but I am getting this error. Any help would me great.

Error

Method 'System.String ToCommonDateString(System.Nullable`1[System.DateTime])' has no supported translation to SQL.

data Service

    public IQueryable<TemplatesJoinAgent> GetTemplateAgentKeyDiseaseId(Guid? agentKey, Guid? diseaseId)
            {
                //Common part
                var TemplatesJoinAgent = (from t in UnitOfWork.GetRepository<Template>().Get(t => t.IsCurrentVersion && t.Status == (short)TemplateMode.Published)
                                          join r in UnitOfWork.GetRepository<Regimen>().Get() on t.Id equals r.TemplateId
                                          join rp in UnitOfWork.GetRepository<RegimenPart>().Get() on r.Id equals rp.RegimenId
                                          join re in UnitOfWork.GetRepository<RegimenEntry>().Get() on rp.Id equals re.RegimenPartId
                                          join a in UnitOfWork.GetRepository<Agent>().Get() on re.AgentVersionKey equals a.VersionKey
                                          select new TemplatesJoinAgent
                                          {
                                              TemplateId = t.TemplateId,
                                              TemplateTitle = t.Title,
                                              GroupTitle = t.GroupTitle,
                                              GuideLineTitle = t.GuideLineTitle,
                                              ExternalDiseaseId = t.ExternalDiseaseId,
                                              DiseaseName = t.DiseaseName,
                                              VersionKey = t.VersionKey,
                                              AgentRxNormTallMan = a.RxNormTallMan,
                                              AgentNccnTallMan = a.NccnTallMan,
                                              AgentName = a.Name,
                                              AgentVersionKey = a.VersionKey,
                                              Added = t.Added,
                                              Modified = t.Modified,
                                              Indication = t.Indications,
    
    
                                          });
    
                TemplatesJoinAgent = TemplatesJoinAgent.Distinct();
    
                return TemplatesJoin
}

controller

 PublishedDate = (t.Modified ?? t.Added).ToCommonDateString(),


public static string ToCommonDateString(this DateTime? d)
{
    return (d.HasValue ? d.Value.ToCommonDateString() : "N/A");
}

CodePudding user response：

The engine does not know how to translate your custom function to SQL. The simplest way to get around that is to add an AsEnumerable() before your projection that uses the custom function. That changes the context from SQL to in-memory and allows custom functions.

The risk is that you want to make sure you have executed as many of your filters as you can before calling AsEnumerable(), otherwise you'll be pulling back more data than you need and filtering in-memory. Of course, if your filters require custom functions then you'll either have to accept that or change your filter to be SQL-compatible.