Thursday, April 12, 2012

LINQ to Entities : this method cannot be translated into a store expression

In LINQ to Entities or LINQ to SQL, if C# user-defined function is used in query expression, this function cannot be translated to SQL expression so the following error can occur.

{"LINQ to Entities does not recognize the method 'System.Collections.Generic.IList`1[System.String] getEmp(Int32)' method, and this method cannot be translated into a store expression."}

Here is an example of calling C# function directly from LINQ query. This will throw an exception since getEmp cannot be converted to a pure SQL statement.

private void WrongRun()
{
    MyDBEntities db = new MyDBEntities();
    var p = from d in db.Depts
            where d.ID == 1
            from emp in getEmp(d.ID)
            select new { d.Name, emp };

    foreach (var x in p)
    {
        Debug.WriteLine(x.Name + ": " + x.emp);
    }
}

public IList getEmp(int deptid)
{
    return new EmpData().GetEmps(deptid);            
}

Assuming there is primary key (Dept.ID) / foreign key (Emp.DeptID) relationship between Dept and Emp table, the problem can go away by rewriting LINQ query as follows.

private void Run()
{
    MyDBEntities db = new MyDBEntities();
    var p = from d in db.Depts
            where d.ID == 1
            from emp in d.Emps
            select new { d.Name, emp.EmpID };

    foreach (var x in p)
    {
        Debug.WriteLine(x.Name + ": " + x.EmpID);
    }
}

The relationship between Dept and Emp is 1 to many, so SelectMany() expression will be used if it is rewritten in LINQ methods.

No comments:

Post a Comment