C# Linq - get objects with not empty collection

Question

I have:

private Dictionary<int, Сolor[]> colorSet = new Dictionary<int, Сolor[]>()
{
   {1, new Сolor[2] {Сolor.Red, Сolor.Green}},
   {2, new Сolor[2] {Сolor.Yellow, Сolor.Blue}},
   ...
};

public class Graph
{
   public Сolor Сolor { get; set; }
   public ICollection<Point> Points { get; set; }
}

1) How I may get List<Graph> from database where Points is not empty?

List<Graph> graphs = context.Graphs.Where(g => g.Points.Count > 0).ToList()

2) How to execute it?

List<Graph> graphs = context.Graphs.Where(g => colorSet[1].Contains(g.Color)).ToList()

The exception is:

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

Using `g.Points.Any()` will produce more efficient queries than `g.Points.Count > 0`. It lets SQL generate `if Exists(select 1 from ...)` queries instead of `if (select count(*) from ...) > 0` queries — Scott Chamberlain, May 27 '15 at 06:51
`colorSet[1].Contains(g.Color)` definitely would not convert to store expression or sql as you are mixing up linq to object with linq to sql. try invoking ToList before where. — Jenish Rabadiya, May 27 '15 at 06:51
@JenishRabadiya doing the .ToList before the where will cause his entire database to be loaded in to ram on the client. Not usually a good idea. — Scott Chamberlain, May 27 '15 at 06:52
@ScottChamberlain Ya that's true but it would not work with mix of linq to object and linq to sql. or he needs to create stored procedure for that. — Jenish Rabadiya, May 27 '15 at 06:54
What is the `Color` class? Is it your own enumeration, an enumeration or an entity? — Panagiotis Kanavos, May 27 '15 at 07:24
Also, which EF version are you using? Enum support was added in EF5 — Panagiotis Kanavos, May 27 '15 at 07:26

score 2 · Answer 1 · edited May 23 '17 at 12:21

2

.Contains can't be used in the .Where clause of a database context. You can use .Any or .All for comparisons.

List<Graph> graphs = context.Graphs.Where(g => colorSet[1].Any(c => c.Color == g.Color)).ToList()

See Using contains() in LINQ to SQL

edited May 23 '17 at 12:21

Community

1
1

answered May 27 '15 at 06:56

Jan Johansen

1,999
6
30
43

Error: 'Color[]' does not contain a definition for 'Any' and the best extension method overload 'System.Linq.Enumerable.Any(System.Collections.Generic.IEnumerable, System.Func)' has some invalid arguments – Actic May 27 '15 at 07:04
Just updated, you need a lambda expression in the .Any and .All methods (or something that returns a bool) – Jan Johansen May 27 '15 at 07:07
1

Actually, `Contains` *can* be used and results in a ` field IN (X,Y,Z)` clause - as in "I use it all the time with EF". It's the `colorSet[1]` that causes the problem. The link points to `String.Contains`, not `IEnumerable.Contains` – Panagiotis Kanavos May 27 '15 at 07:14
1

@PanagiotisKanavos you are right. mixture of linq to sql and linq to object would not work anyway. – Jenish Rabadiya May 27 '15 at 07:19
It seems, in one step data can't be selected. Thks. – Actic May 27 '15 at 07:29
@Actic, no - it can. You just have to write this properly – Panagiotis Kanavos May 27 '15 at 07:39

Panagiotis Kanavos · Accepted Answer · 2015-05-27T09:34:57.713

There is no problem with using Enumerable.Contains() in EF. It's String.Contains that can't be translated.

The problem with your second query is that you mix LINQ operators with object access code, specifically colorSet[1]. LINQ to EF will not try to execute this code and doesn't know how to translate it to SQL anyway.

The solution is to store the colorSet value to variable first:

var colors = colorSet[1];
List<Graph> graphs = context.Graphs
                            .Where(g => colors.Contains(g.Color))
                             .ToList();

LINQ to EF knows to translate Enumerable<T>.Contains() to AND Color IN (0,1) clause

This assumes that you use at least EF 5, Color is an enum and Point a custom class, eg:

public enum Color
{
    Red,
    Green,
    Blue,
    Yellow
}
public class Point
{
    public int Id { get; set; }
    public int X { get; set; }
    public int Y { get; set; }
}

UPDATE

As for the first question, you can retrieve graphs that have any points by using Any():

var graphs=context.Graphs
                  .Where(g => g.Points.Any())
                  .ToList();

Linq to EF will generate a SQL statement with a WHERE EXISTS clause eg

WHERE  EXISTS (SELECT 
    1 AS [C1]
    FROM [dbo].[Points] AS [Extent2]
    WHERE [Extent1].[Id] = [Extent2].[Graph_Id]
)

This will return the graphs that have points but not the points themselves. These will be loaded in a lazy manner when you try to access the Points property of a Graph object. This can be a performance gain if you want to access only a few Points properties but will result in numerous queries if you want to access all of them (the N+1 problem)

If you want to load the Points as well, you need to use the Include() method, eg:

var graphs=context.Graphs
                  .Include(g => g.Points)
                  .Where(g => g.Points.Any())
                  .ToList();

This will execute a left join between the graphs and points and return all of the data in a single query.

Not absolutely that is necessary. Selections is executed in big expression. But thks. — Actic, May 27 '15 at 10:09

C# Linq - get objects with not empty collection

2 Answers2