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."

Answer 1

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

Answer 2

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));