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.

Answer 1

.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

Answer 2

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.