So the question is ridiculously long, so let's go to the code. What's the linq2entities equivalent of the following Sql, given entities (tables) that look like:
Parent
---
parent_id
parent_field1
Child
--
child_id
parent_id
child_field1
child_field2
The sql:
select p.*, c.*
from parent p
inner join p on
p.parent_id = child.parent_id
where
c.child_field1 = some_appropriate_value
order by
p.parent_field1
c.child_field2
L2E let's you do .include() and that seems like the appropriate place to stick the ordering and filtering for the child, but the include method doesn't accept an expression (why not!?). So, I'm guessing this can't be done right now, because that's what a lot of articles say, but they're old, and I'm wondering if it's possible with EF6.
Also, I don't have access to the context, so I need the lambda-syntax version.
I am looking for a resultant object hierarchy that looks like:
Parent1
|
+-- ChildrenOfParent1
|
Parent2
|
+-- ChildrenOfParent2
and so forth. The list would be end up being an IEnumerable. If one iterated over that list, they could get the .Children property of each parent in that list.
Ideally (and I'm dreaming here, I think), is that the overall size of the result list could be limited. For example, if there are three parents, each with 10 children, for a total of 33 (30 children + 3 parents) entities, I could limit the total list to some arbitrary value, say 13, and in this case that would limit the result set to the first parent, with all its children, and the second parent, with only one of its children (13 total entities). I'm guessing all of this would have to be done manually in code, which is disappointing because it can be done quite easily in SQL.
according to your comment, this is what you want:
when you get a query from
db
usingentityframewrok
to fetch parents, parent's fields are fetched in single query. now you have a result set like this:then, if you have a
foreign key
on parent,entityframework
creates anavigation property
on parent to access tocorresponding
entity
(for exampleChild
table).in this case, when you use this
navigation property
fromparent entities
which already have been fetched, to getchilds
,entityframework
creates another connection tosql server
per parent.for example if count of
parentsQuery
is15
, by following queryentityframework
creates15
another connection, and get15
anotherquery
:in these cases you can use
include
to prevent extra connections to fetch allchilds
with itsparent
, when you are trying to get parents in single query, like this:then by following
Query
,entityframework
doesn't create any connection and doesn't get any query again :but, you can't create any condition and constraint using include, you can check any constraint or conditions after include using
Where
,Join
,Contains
and so forth, like this:but by this query, all child have been fetched from
database
beforethe better way to acheieve equivalent sql query is :