LINQ to Entities - method cannot be translated into a store expression

Question

The same query worked in .Net 3.5 but not in .Net 4.5.2 There are lots of posts here with the same error, and tried almost all but of no use. I have extracted everything into a separate variable for querying. Still i am getting the error -

LINQ to Entities does not recognize the method 'System.String Format(System.String, System.Object)' method, and this method cannot be translated into a store expression.

 private void LoadAppointmentData()
        {
            var user = Session["user"].ToString();
            var userFirstName = db.Users.SingleOrDefault(u => u.FirstName == user);

            var userFN = userFirstName.username;
            var chwWorker = from c in db.PatientContacts
                            where c.UserName == userFN &&
                                 (c.PCP_Status == "Appointment Made" || c.PCP_Status_AWC == "Appointment Made"
                                   || c.PCP_Status_AWDV == "Appointment Made" ||
                                 (c.PCP_Status == "RX for Mamogram" && c.Status == "Appointment Made")) 
                            orderby c.PCP_Status_Date descending 
                            select new
                            {
                                Id = c.MemberID,
                                Name = c.PatientFirstName + " " + c.PatientLastName, 
                                PCP_Appt = $"{c.PCP_Status_Date:d}",
                                Mammogram_Appt = $"{c.StatusDate:d}",
                                Phone = GetPhone(c.MemberID)
                            };
            if (chwWorker.Any())
            {
                if (grvAppointmentList != null)
                {
                    pnlAppointmentFollowUp.Visible = true;
                    grvAppointmentList.DataSource = chwWorker;
                    grvAppointmentList.DataBind();
                }
            }
}

I am not sure what else to change to make this query run.

`$"{c.PCP_Status_Date:d}"` [is `String.Format`](https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/keywords/interpolated-strings). I assume you know what the error means. — Gert Arnold, Oct 28 '17 at 21:27
Nope, string.Format has never been a supported method in Entity Framework. I assume you used LINQ-to-SQL in .Net 3.5, which auto-switched to client-side evaluation for parts in the LINQ query that couldn't be translated into SQL. — Gert Arnold, Oct 28 '17 at 21:42
I tried converting to PCP_Appt = string.Format("{0:d}", c.PCP_Status_Date), and still getting the same error - " Message "LINQ to Entities does not recognize the method 'System.String Format(System.String, System.Object)' method, and this method cannot be translated into a store expression." s — Ron, Oct 29 '17 at 16:03
@GertArnold, Yes you are right, I used LINQ-to-SQL in .Net 3.5, — Ron, Oct 29 '17 at 16:06
There are tons of posts explaining the meaning of "LINQ to Entities does not recognize the method...". As usual: let EF get the data, do formatting later in memory. — Gert Arnold, Oct 29 '17 at 16:12

score 2 · Accepted Answer · edited Oct 31 '17 at 12:47

You need to use "LINQ to Objects" to perform string.Format or interpolated strings by using AsEnumerable() or ToList() before using Select:

var chwWorker = (from c in db.PatientContacts
                where c.UserName == userFN &&
                     (c.PCP_Status == "Appointment Made" || c.PCP_Status_AWC == "Appointment Made"
                      || c.PCP_Status_AWDV == "Appointment Made" ||
                     (c.PCP_Status == "RX for Mamogram" && c.Status == "Appointment Made")) 
                orderby c.PCP_Status_Date descending select c) 
                .AsEnumerable() // or 'ToList()'
                .Select(c => new 
                {
                     Id = c.MemberID,
                     Name = c.PatientFirstName + " " + c.PatientLastName, 
                     PCP_Appt = $"{c.PCP_Status_Date:d}",
                     Mammogram_Appt = $"{c.StatusDate:d}",
                     Phone = GetPhone(c.MemberID)
                });

Note that string.Format method is not recognized by LINQ to Entities to translate it as an SQL command, hence query result materialization to memory is necessary.

NB: You can use SqlFunctions.StringConvert if you still want LINQ to Entities query before using Any() method, but not all SQL providers able to translate it into SQL statement.

Related issue:

LINQ to Entities does not recognize the method 'System.String Format

Thanks @Tetsuya Yamamoto - I had to use the Select operator for the query to work like this "... orderby c.PCP_Status_Date descending select c) .AsEnumerable()..." and it works fine now. Other wise, it's throwing a "A query body must end with select clause or group clause. Thanks for your help in resolving this. — Ron, Oct 30 '17 at 22:10

LINQ to Entities - method cannot be translated into a store expression

1 Answers1