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
– Scott Chamberlain, Commented May 27, 2015 at 6: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
– Jenish Rabadiya, Commented May 27, 2015 at 6: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
– Scott Chamberlain, Commented May 27, 2015 at 6: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
– Jenish Rabadiya, Commented May 27, 2015 at 6:54
What is the Color class? Is it your own enumeration, an enumeration or an entity? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented May 27, 2015 at 7:24

Panagiotis Kanavos · Accepted Answer · 2015-05-27 09:34:57Z

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.

Community · Accepted Answer · 2017-05-23 12:21:52Z

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, 2017 at 12:21

CommunityBot

11 silver badge

answered May 27, 2015 at 6:56

Jan Johansen

2,0446 gold badges32 silver badges43 bronze badges

6 Comments

Actic Over a year ago

Error: 'Color[]' does not contain a definition for 'Any' and the best extension method overload 'System.Linq.Enumerable.Any<TSource>(System.Collections.Generic.IEnumerable<TSource>, System.Func<TSource,bool>)' has some invalid arguments

Jan Johansen Over a year ago

Just updated, you need a lambda expression in the .Any and .All methods (or something that returns a bool)

Panagiotis Kanavos Over a year ago

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<T>.Contains

Jenish Rabadiya Over a year ago

@PanagiotisKanavos you are right. mixture of linq to sql and linq to object would not work anyway.

Actic Over a year ago

It seems, in one step data can't be selected. Thks.

|

Collectives™ on Stack Overflow

C# Linq - get objects with not empty collection

2 Answers 2

1 Comment

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related