Skip to main content

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

While working with LINQ queries (IQueryable<T>) one may face the error of 'LINQ to Entities does not recognize the method...’

For example consider following LINQ statement:

dbContext.Employees.Where(c => !string.IsNullOrWhiteSpace(c.Name));

There will be not any compile error and it'll be compiled successfully, however you'll get the error (mentioned below) when this query get executed.

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

Reason: Once you get the error, you'll try to figure out what went wrong with this LINQ statement, as there is no error if you compile the above statement.
Simple reason of this error is IQueryable<T> creates the SQL friendly query and once you enumerate (i.e. by accessing any property or converting it into IEnumerable<T> or List) over this query it'll get executed. That means every LINQ statement written by you is then converted to relevant SQL statement, and when you try to use static method of string like 'IsNullOrWhiteSpace', 'IsNullOrEmpty' etc., it won't be translated into SQL query. Because these methods has no supported translation to SQL.

Solution: As I mentioned earlier LINQ to Entities queries are translated to SQL statements, but there is no way to translate the logic of your custom comparer to SQL statement, so to avoid the error either we need to modify the LINQ statement so that it can easily be translated into SQL or the custom comparisons has to be done in memory.

Solution1 -> Modify your statement to avoid error:

We can modify above mentioned statement by replacing:

!string.IsNullOrWhiteSpace(c.Name)

to

!(c.Name == null || c.Name.Trim() == string.Empty)

And this statement will be easily translated into LINQ to Entities or LINQ to SQL.

Solution2 -> Convert to IEnumerable<T> and then do the custom operation in memory.

Modify the statement as:

dbContext.Employees.AsEnumerable()
            .Where(c => !string.IsNullOrWhiteSpace(c.Name));

Second approach have a drawback though for example if you are trying to filter out from thousands or millions records through the where clause if you convert your query into IEnumerable<T> query will be executed on to the SQL and all data will be stored in the memory for further operations so every time you fire this query you need a good amount of memory for your custom operation and thus there will be performance issue but in case of "Solution 1" first query is generated with all the necessary filters and then only matched data will be returned back instead of all records. So it’s all on you which solution is good for your requirements

Hope that helps J

Popular posts from this blog

C#: Merging Excel cells with NPOI HSSFWorkbook

In this post we’ll see how to merge the two or more cell with each other while creating the excel sheet using NPOI.
Mentioned below is code to merge multiple cells, in this example we are merging first cell to fifth cell of first row (you can adjust row or cell range by passing particular parameters in CellRangeAddress).
//Created new Workbook var hwb = new NPOI.HSSF.UserModel.HSSFWorkbook(); //Create worksheet with name. var sheet = hwb.CreateSheet("new sheet"); //Create row and cell. var row = sheet.CreateRow(0); var cell = row.CreateCell(0); ; //Set text inside cell cell.SetCellValue("This is Merged cell"); cell.CellStyle.WrapText = true; //define cell range address // parameters: -> first row to last and first cell to last cell var cra = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 4); //Add merged region to sheet. sheet.AddMergedRegion(cra);
Hope this solution helps you J

Calculate Sum and average of two or more Textbox values using JQuery

We can calculate sum and average of values entered in textboxes with the help of small piece of jQuery code.
First of all let’s create a HTML form where we’ll ask user to enter marks in each subject, under body section add following HTML:
<tableclass="demo-table"> <trclass="label"> <tdcolspan="2">Please enter marks(0-99):</td> </tr> <tr> <td>English</td> <td> <inputtype="text"class="txtMarks"name="txtMarks"

Difference between Web API, WCF and Web Service

So now we have got the basic idea about Web API, now let’s do some comparison of Web API with WCF and web services.
Web Service WCF Web API Web services created as file with .asmx extension. WCF create with .svc extension Web API are simple class file with .cs(for C#) extension. Web API is inherited from “ApiController” and class name must end with “Controller”. It is SOAP based service and return data in XML form. It is also based on SOAP and return data in XML form. Web API is HTTP based service and by default it return data in JSON or XML form. It supports only HTTP protocol. It supports various protocols like TCP, HTTP, HTTPS, Named Pipes, and MSMQ. It supports HTTP protocol. It can be hosted only on IIS. It can be hosted with in the application or on IIS or using window service. It can be hosted with in the application or on IIS. It is not open source but can be consumed by any client that understands xml. It is not open source but can be consumed by any client t…