Join array to EF query

Question

I get the following error when trying to join an array to the Linq-to-EF query

An error occurred while executing the command definition. See the inner exception for details. Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.

The code is as follows:

var vids = new List<string>();
using (var ctx = new MyDbContext())
{
    var qry = ctx.Pickups.Where(p => p.UserName == User.Identity.Name);
    if (someBoolean)
    {
        var v = GetVids(); // get the list of Ids from a web service
        vids.AddRange(v);
    }
    if (vids.Count() > 0)
    {
        qry = qry.Join(vids, p => p.VId, v => v, (v, p) => p);
    }
    var data = qry
        .Select(p => new
        {
            // etc.
        });
}

The problem is that the web service is not associated with the DB I'm using EF with, otherwise, I'd just do the join against the tables in the DB. The number of Id's I get back from the web service could be upwards of a hundred or so (usually 5-10). If I comment out the Join, the code works fine, so I know the error is in the Join. With just a few (up to about 30) ids in vids, the join works perfectly.

What do you recommend to remedy this problem? The only thing I could think of was to insert the list of IDs into the DB and do the join that way. That doesn't seem too appealing to me.

I think the problem is not with the parts of code you have provided — Maziar Taheri, Jun 18 '11 at 18:33

score 5 · Accepted Answer · edited May 23 '17 at 10:24

5

Try to replace if (vids.Count() > 0) with:

if (vids.Count > 0)
{
    qry = qry.Where(arg => vids.Contains(arg.VId));
}

This will work only if vids is less then 2100 elements, as this will be translated to IN (x, y, .., n) condition.

If you use entity framework 3.5, then Contains will not work. You can find possible solution here: 'Contains()' workaround using Linq to Entities?

edited May 23 '17 at 10:24

Community

1
1

answered Jun 18 '11 at 18:32

Alex Aza

76,499
26
155
134

I'm using EF 4.0, so I'm OK. Changed the Join to the Where you described and it works great. Won't ever have more than a few hundred, so this should work. Ahh, the joys of dealing with two disparate systems and having to be the aggregator myself. – Ed Sinek Jun 18 '11 at 18:43

Join array to EF query

1 Answers1