score:1
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!
Source: stackoverflow.com
Related Query
- Query with LINQ where Context matches multiple parameters from a List of Objects
- Using a Linq query to select objects where any value of a property matches values from a list
- linq to sql query with multiple where parameters
- Linq query for updating list of objects with some data from another list of objects
- LINQ - one bool from multiple conditions of a list with objects
- Multiple Grouping with IQueryable and returning a list of objects in C# linq query
- Select values from multiple dictionaries into list of objects with LINQ
- Filter query by multiple parameters from the list of objects
- LINQ query list of objects with multiple list of objects in it
- Using a list of Json results as parameters for a mvc actionresult, to return objects from database with Linq and Lambda
- linq query on multiple columns from a list of objects
- How to construct a LINQ Query to test a list against another list where elements start with the elements from the other
- Filter ICollection of objects where any criteria matches the ones from a list of ints usin Linq
- LINQ query to return distinct field values from list of objects
- LINQ query with a WHERE clause with multiple conditions
- LINQ to SQL: Complicated query with aggregate data for a report from multiple tables for an ordering system
- Linq to entities - SQL Query - Where list contains object with 2 properties (or more)
- Entity Framework Core LINQ query with multiple where clauses
- LINQ Filter query with multiple optional parameters
- linq remove items from query where any list value is present
- Query in returning Objects from List using LINQ
- Linq query and multiple where clauses with OR only first condition gets evaluated
- C# - Linq optimize code with List and Where clause
- How to select multiple columns from dataset into a string list with LinQ
- LINQ query Where ID does not exist as a property in a list of objects
- Using LINQ with MVC, how can I include multiple parameters into a where clause?
- How to get number of objects with common properties from context using LINQ
- Using linq group by to get from a list of objects with a DateTime property to a list with an interval StartDate, EndDate
- LINQ query to filter list by object property with multiple checks
- Linq select from list where property matches a condition
More Query from same tag
- Linq group by bahaving strangely
- C# Linq Reading File into List
- Ndepend find actual number of method usages
- Multiple linq "from" and variables visibility
- Rewrite this foreach yield to a linq yield?
- set DataSource for DataGridView
- Cast exception when using IncludeFilter with EF6
- Using the Except clause for two DataTable objects
- Multiple descendants types linq
- Remove items from collection using linq
- Pulling Data from 1 DataContext to use in another's Method
- LINQ Statement Optimization - Seeking advice for better design
- SQL query with subquery equivalent for Linq-to-SQL
- How to add item to Model's ICollection List Property
- System.Data.Linq.DuplicateKeyException on empty table
- Empty Sequence in LINQ
- How to check if XML contains element when using LINQ to XML?
- How can I sort a list of Employees by the SortOrder of their Roles?
- Add Remove Update List of class in C#
- Access columns in ItemDataBound event when the datasource is Linq
- Left outer join to perform DataTable Comparison using Linq
- Possible to use anonymous type members during declaration?
- Balancing oriented graph
- Is there an easy way to check if all elements in ICollection<ISet<string>> are identical?
- Quering with Linq in c#
- Grouping by multiple keys known at runtime
- LINQ to SQL column has keyword as column name, how to quote/escape
- Listing Elements of Array / List<String>
- KeyValuePair from running Raw SQL query on Entity Framework
- How do I use LINQ to find differences between 2 lists