2

I need to retrieve a list of entities from my database that matches a list of items in a plain list (not EF). Is this possible with Entity Framework 4.1?

Example:

var list = new List<string> { "abc", "def", "ghi" };
var items = from i in context.Items
            where list.Contains(i.Name)
            select i;

This works great to return rows that match one property, but I actually have a more complex property:

var list = new List<Tuple<string, string>>
{
    new Tuple<string,string>("abc", "123"),
    new Tuple<string,string>("def", "456")
};

// i need to write a query something like this:
var items = from i in context.Items
where list.Contains(new Tuple<string,string>(i.Name, i.Type))
select i;

I know that is not valid because it will say it needs to be a primitive type, but is there any way to do what I'm trying to accomplish or will I need to resort to a stored procedure?

Dismissile
  • 32,564
  • 38
  • 174
  • 263

3 Answers3

0

You need to break it down to sub-properties. For example, something like (this might not compile, i'm not able to test at the moment, but it should give you something to work with):

var items = from i in context.Items 
where list.Select(x => x.Item1).Contains(i.Name) 
   && list.Select(x => x.Item2).Contains(i.Type)
select i;
Erik Funkenbusch
  • 92,674
  • 28
  • 195
  • 291
  • Does not work. Errors when trying to execute. Unable to create a constant value of type Test.Pair. Only primitive types are supported in this context. – Dismissile Nov 08 '11 at 16:29
0

You have a few options:

1) You could, of course, write a stored procedure to do what you need and call it.

2) You could read the table into memory and then query the in memory list...that way you don't have to use primitives:

var items = from i in context.Items.ToList()
            where list.Contains(new Tuple<string, string>(i.Name, i.Type))
            select i;

3) You could also convert your query to use primitives to achieve the same goal:

var items = from i in context.Items
            join l in list
            on new { i.Name, i.Type } equals 
                new { Name = l.Item1, Type = l.Item2 }
            select i;

I would go with the second option as long as the table isn't extremely large. Otherwise, go with the first.

Justin Niessner
  • 242,243
  • 40
  • 408
  • 536
  • @Dismissile - What doesn't work about it? Not the expected results or an error? – Justin Niessner Nov 08 '11 at 16:19
  • @JustinNiessner It gives an error when trying to execute the query. It says it needs a primitive type. I don't think it is smart enough to send a list of string/string pairs to the query. – Dismissile Nov 08 '11 at 16:23
  • System.NotSupportedException was unhandled Message=Unable to create a constant value of type 'Test.Pair'. Only primitive types ('such as Int32, String, and Guid') are supported in this context. – Dismissile Nov 08 '11 at 16:26
  • @JustinNiessner I think it's going to have to be #1. There are way too many entities to bring back an in-memory list and query from that. I think I will use a table valued parameter to send to the stored proc which should make it easy to join and get the results I need. – Dismissile Nov 08 '11 at 16:30
0

You have to think about what the resulting SQL would look like, this would be difficult to do directly in SQL.

My suggestion would be you split out one field of the tuples and use this to cut down the results list, get back the query result then filter again to match one of the tuples e.g.

var list = new List<string> { "abc", "def" };
var list2 = new List<Tuple<string, string>>
{
  new Tuple<string,string>("abc", "123"),
  new Tuple<string,string>("def", "456")
};
var items = (from i in context.Items
        where list.Contains(i.Name)
        select i)
        .AsEnumerable()
        .Where(i => list2.Any(j => j.val1 == i.Name && j.val2 == i.Type);
James Ellis-Jones
  • 3,042
  • 21
  • 13
  • Not really a workable solution for my case. Name and Type basically form a unique key for me. Name by itself would return way too many rows. I think I'm going to have to create a stored procedure and use a table valued parameter. – Dismissile Nov 08 '11 at 16:28
  • Or you could write your tuples into a temporary table and do a database join – James Ellis-Jones Nov 08 '11 at 16:40