need to use Count() for null and non null values in Linq

Question

Here is my code:

var value = query.Select(
    a => new CHART_MODEL
        {
            TEXT = "xyz",
            COUNT1 = (a.TOPLAM_FIYAT != null).ToString().Count(),
            COUNT2 = (a.TOPLAM_FIYAT == null) ? ToString().Count() : 0
        }
     ).ToList();

Here is the error:

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

and CHART_MODEL

public class CHART_MODEL
{
  public int COUNT1 { get; set; }
  public int COUNT2 { get; set; }
  public string TEXT { get; set; }
}

My question is: I have to calculate null and non null values with Count(). but it doesn't allow me to write. I don't know how to rearrange linq query code in correct way with Count().

see: https://stackoverflow.com/questions/18233495/linq-to-entities-does-not-recognize-the-method-system-string-tostring-method and: https://stackoverflow.com/questions/1066760/problem-with-converting-int-to-string-in-linq-to-entities — Adam Vincent, May 29 '21 at 13:07
Does this answer your question? [Problem with converting int to string in Linq to entities](https://stackoverflow.com/questions/1066760/problem-with-converting-int-to-string-in-linq-to-entities) — Adam Vincent, May 29 '21 at 13:07
I don't understand why you're trying to use `ToString()` here at all. If you want to count something, why is `ToString` involved? — JLRishe, May 29 '21 at 14:05

Ran Turner · Answer 1 · 2021-05-29T13:39:04.587

This is an error you get because EF doesn't know how to execute the .ToString() method in sql.

Methods like that which EF doesn't have correlated methods in plain sql, will throw an error like the one you received. What you can do is use an Immediate Execution.methods like: ToList or ToArray will force the execution of the query, hence loading the data into memory and when the data is loaded the rest of the operators are performed using Linq to objects on that the data that was brought in memory.

So, you can load the data using toList() and after that use the rest of the code and methods like toString() won't throw an error.

query.toList().Select(..)

You can read more about Deferred Execution vs Immediate Execution here: https://samirbehara.com/2016/01/04/deferred-execution-vs-immediate-execution-in-linq/

sgmoore · Answer 2 · 2021-05-29T14:08:38.977

The simplest way would be something like

var value = new  CHART_MODEL
{
    TEXT = "xyz",
    COUNT1 = query.Where(a=>a.TOPLAM_FIYAT != null).Count(),
    COUNT2 = query.Where(a=>a.TOPLAM_FIYAT == null).Count() 
};

Note, this will issue two select statements.

If you really want to avoid the two select statements, you have to introduce a dummy grouping such as

var value = (from r in query
         group r by 1 into results
         select new  CHART_MODEL
         {
             TEXT = "xyz",
             COUNT1 = results.Where(a => a.TOPLAM_FIYAT != null).Count(),
             COUNT2 = results.Where(a => a.TOPLAM_FIYAT == null).Count()
         }).Single();

score 0 · Answer 3 · answered May 31 '21 at 15:47

Just complementing on @sgmoore's answer, you can place you filter condition inside the .Count method itself:

var value = new  CHART_MODEL
{
    TEXT = "xyz",
    COUNT1 = query.Count(a => a.TOPLAM_FIYAT != null),
    COUNT2 = query.Count(a => a.TOPLAM_FIYAT == null) 
};

There is the new is null and is not null syntax introduced in C#9. It reads really nice, but it is still not recommended by Microsoft to use in Linq-to-SQL.

need to use Count() for null and non null values in Linq

3 Answers3