The error message
The LINQ expression 'DbSet<Project>()
.Join(
inner: __p_0,
outerKeySelector: p => p.ProjectId,
innerKeySelector: t => t.ProjectId,
resultSelector: (p, t) => new ProjectDateModel{
ProjectId = p.ProjectId,
ProjectName = p.ProjectName,
Date = t.Date
}
)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
Here is my ef model
public partial class Task
{
public int TaskId { get; set; }
public int EmployeeId { get; set; }
public int ProjectId { get; set; }
public DateTime? Date { get; set; }
}
public partial class Project
{
public int ProjectId { get; set; }
public string ProjectName { get; set; }
}
What am trying to achieve here is, select all the tasks in the specific month and select all the projects by task.ProjectId and project.ProjectId My linq expression
var date = new DateTime(year, month, 1);
var projectModel = new List<ProjectDateModel>();
for (int i = 0; i < DateTime.DaysInMonth(year, month); i )
{
var tasks = _context.Task.Where(t => t.Date == date && a.EmployeeId == employeeId).ToList();
var projects = _context.Project.Join(tasks, p => p.ProjectId, t => t.ProjectId, (p, t) => new ProjectDateModel {
ProjectId = p.ProjectId,
ProjectName = p.ProjectName,
Date = a.Date
}).ToList();
projectModel.AddRange(projects);
date = date.AddDays(1);
}
return (projectModel);
ProjectDateModel
public class ProjectDateModel
{
public int ProjectId { get; set; }
public string ProjectName { get; set; }
public DateTime? Date { get; set; }
}
CodePudding user response:
John - The issue is likely that you perform one EF query and call ToList, then try to join that local list with another EF query. It's all LINQ but you're mixing two different providers: LINQ to Objects and LINQ to Entities. Your EF query needs to be able to be translated to SQL code to execute on the database but your local list doesn't exist on the database. Get rid of the ToList call on the first query and I suspect that it will work.
CodePudding user response:
Try that way , iam just not sure about date prop
var tasks = _context.Tasks.Where(x => x.Date == date && x.EmployeeId == employeeId).ToList();
var taskIds = tasks.Select(x => x.ProjectId);
var projects = _context.Projects
.Where(x => taskIds.Contains(x.ProjectId))
.Select(x => new ProjectDateModel
{
ProjectId = x.ProjectId,
ProjectName = x.ProjectName,
Date = tasks.FirstOrDefault(y => y.ProjectId == x.ProjectId).Date,
});