LINQ to Entities does not recognize the method ,and this method cannot be translated into a store expression

Question

I have following code which get all data using EF and then try converting them to Model as below.

var patients = allpatients.Select(p => CreatePatient(p));

    public Patient CreatePatient(PATIENT p)
        {
            Patient patient = new Patient();

            patient.FIRSTNAME = p.FIRSTNAME;
            patient.MIDDLENAME = p.MIDDLENAME;
            patient.SURNAME = p.SURNAME;

            return patient;
        }

But getting this error

"LINQ to Entities does not recognize the method 'Model.Patient CreatePatient(Repository.PATIENT)' method, and this method cannot be translated into a store expression."

score 4 · Answer 1 · answered Jul 26 '17 at 07:56

This statement can't be directly translated to any equivalent SQL commands:

var patients = allpatients.Select(p => CreatePatient(p));

Common way to use custom extension methods in LINQ to Entities (EF data context) is perform query on the model first (which able to be translated into SQL statements), then use custom extension method outside query context (this is just an example):

var patients = allpatients.Select(p => new Patient() 
{
     FIRSTNAME = p.FIRSTNAME,
     MIDDLENAME = p.MIDDLENAME,
     SURNAME = p.SURNAME
});

foreach (Patient pt in patients)
{
    // iterate through Patient collection
    // use your custom method here
}

Note that the same error also occurs if the custom method becomes part of the new model assignment inside LINQ query like this example:

var patients = allpatients.Select(p => new Patient() 
{
     PATIENTID = ToInt32(p.PATIENTID), // ToInt32 is a custom extension method, e.g. converting string to int
     FIRSTNAME = p.FIRSTNAME,
     MIDDLENAME = p.MIDDLENAME,
     SURNAME = p.SURNAME
});

This is the correct way of above usage:

var patients = allpatients.Select(p => new Patient() 
{
     PATIENTID = p.PATIENTID, // leave the selected property as-is
     FIRSTNAME = p.FIRSTNAME,
     MIDDLENAME = p.MIDDLENAME,
     SURNAME = p.SURNAME
});

// another method content
foreach (Patient pt in patients)
{
    other.PATIENTID = ToInt32(pt.PATIENTID);
}

Reference:

LINQ to Entities does not recognize the method

score 3 · Answer 2 · answered Jul 26 '17 at 07:08

3

You can just create the new Patient objects in the LINQ select:

var patients = allpatients.Select(p => new Patient()  {
            FIRSTNAME = p.FIRSTNAME,
            MIDDLENAME = p.MIDDLENAME,
            SURNAME = p.SURNAME
        });

Or define a Patient constructor that accepts another Patient object and initializes itself with the values of the provided Patient:

public partial class Patient
{
    public Patient(Patient p)
    {
        this.FIRSTNAME = p.FIRSTNAME;
        this.MIDDLENAME = p.MIDDLENAME;
        this.SURNAME = p.SURNAME;
    }
}

And then use that in the LINQ select:

var patients = allpatients.Select(p => new Patient(p));

answered Jul 26 '17 at 07:08

Romano Zumbé

7,893
4
33
55

I can, but I am tryinng to use Model Factory and will be moving create method to another class. I am trying to understand why the error. – Simsons Jul 26 '17 at 07:23
The error simply tells that `CreatePatient` method can't be translated to SQL statement & you need to put it out of query context. – Tetsuya Yamamoto Jul 26 '17 at 07:24

score 2 · Answer 3 · answered Jul 31 '17 at 10:28

In this case Select method is extension method for the IQuerable interface. This method expects argument to be an expression and not a function. When you iterate over the collection, expression tree is parsed by LINQ to Entities (in this case), and transformed to sql (for LINQ to Entities). And LINQ to entites simply doesn't know how to convert your function to sql.

What you can though is to fetch the data first and then apply your functon to each element in the colection. To do that you can all ToArray method:

var patients = allpatients.ToArray().Select(p => CreatePatient(p));

Or iterate over the collection:

foreach (Patient pt in allpatients)
{
 //do whatever you want here
}

The drawback here is that you load all columns, so if you want to fetch only specific ones you can use anonymous objects:

var patients = allpatients.Select(p => new { FirstName = p.FIRSTNAME }).ToArray().Select(p => CreatePatient(p));

Or create a class for dto object, for instance if i want to get only first name:

class PersonFirstNameDto  {
    public string FirstName { get; set; }
}

var patients = allpatients.Select(p => new PersonFirstNameDto { FirstName = p.FIRSTNAME }).ToArray().Select(p => CreatePatient(p));

LINQ to Entities does not recognize the method ,and this method cannot be translated into a store expression

3 Answers3