score:1

Accepted answer

i solved the problem by myself. not that perfect, but it's working.

i created a new database for my webservice with a table "requestarticles":

create table dbo.requestarticle(
    requestid uniqueidentifier not null,
    producerid int not null,
    barcode nvarchar(30) not null
)

i wrote my requests into that table:

guid requestguid = guid.newguid();
foreach (var requestarticle in requestedarticles.articles) requestarticle.requestid = requestguid;
_context.requestarticle.addrange(requestedarticles.articles); //ienumerable list of articles
_context.savechanges();

after that i just manually joined my new request-table.

iqueryable<articles> query = _context.articles.fromsql<articles>(
    @"select      a.*, ra.barcode as requestedbarcode
      from        dbo.articles a
      inner join  webservice.dbo.requestarticle ra
              on  ra.producerid = a.producerid and ra.barcode = a.barcode
      where       ra.requestid = {1}",
    requestguid);

at the moment i am suffering to replace the new static database-name within the sql-string with a different string from my new context, but the basics work fast like a charm. even though it's not the solution i was hoping for, as i thought linq+ef are smarter than that. :-)

score:3

unfortunately, this is a situation that normal linq to entities code can't handle very well.

the syntax you'd want to use looks like this:

iqueryable<article> query = _context.article
    .where( p => 
       p.active == true &&
       articlequery.articlerequest.any(
           r => r.producerid == p.producerid && 
                r.articlerequest.barcode == p.barcode
       )
    );

unfortunately, linq to entities can't handle mixing in-memory collections into database queries like this.

in theory, you could represent your articlerequest as a model in the database and perform a query similar to the one above, but based on an iqueryable<articlerequest> from your context rather than just a parameter variable. but that would be a terribly hacky workaround.

some people resort to materializing the entire set of article objects so they can use a linq to objects provider:

iqueryable<article> query = _context.article
    .asenumerable() // danger: materializes *all* the articles
    .where( p => 
       p.active == true &&
       articlequery.articlerequest.any(
           r => r.producerid == p.producerid && 
                r.articlerequest.barcode == p.barcode
       )
    );

but this approach won't scale well as you get more articles.

another approach is to build expressions trees dynamically, which would result in an expression that looks like this:

    p => 
       p.active == true &&
       ((p.producerid == 12 && p.barcode == "12345") ||
        (p.producerid == 13 && p.barcode == "54321") ||
        ...
       )

but the code required to produce this kind of statement dynamically is pretty ugly and very difficult to understand.

a better solution would probably be to use a bunch of unions. one concise way to represent that is:

iqueryable<article> query = articlequery.articlerequest.aggregate(
    _context.article.where(p => false),
    (q, r) => q.union(context.article.where(p => 
           r.producerid == p.producerid && 
           r.articlerequest.barcode == p.barcode))
)
    .where(p => p.active == true);

this will produce a sql query that unions the results of several one-off queries against each other. with a little more c# code, you could remove the superfluous "where false" part of the query.

there are other libraries like dynamic linq and linqkit which are designed to help with this sort of problem, but it's been a long time since i've played with them and i can't vouch for how well they'll work against modern versions of entity framework.

alternatively, you can avoid using linq entirely and just construct some custom sql for this one query. that's probably what i'd do. just be careful to guard against sql injection on that barcode!


Related Query