How to retrieve associated entities using sprocs?

[Yash Ganthe]

Our Meeting entity is linked to the Attendee entity through a 1-to-
many relation defined in the EDMX file. When I obtain the list of
attendees for a given meeting as myMeeting.Attendees, they come from a
SQL join between the tblMeeting and tblAttendee tables, which Entity
Framework figures out. However, I would like to use a custom stored
proc that lists Meetings along with their Attendees which are computed
using some logic in the stored proc. I would like to use a temp table
in the sproc and apply various other intermediate computations to
arrive at the meetings and their associated attendees. I want to
filter out attendees linked to meetings at the DB layer and not at the
business layer for performance reasons.
This is just one of the cases. There are several other entities for
which I need customized computation of related entities to be done at
the DB layer before returning the data to the business layer.

The dev experience could be like:
MyEntities ent = new MyEntities ();
IEnumerable<Meeting> meetings = ent.MeetingSet.Include
("Attendees").UseSproc("spLoadMeetings").AsEnumerable<Meeting>();

foreach(Meeting myMeeting in meetings)
{
foreach(Attendee a in myMeeting.Attendess)
{
//My code
}
}


Is this supported in Entity Framework? Is it part of EF 4.0?

Thanks,
Yash

 

[Gregory A. Beamer]

Our Meeting entity is linked to the Attendee entity through a 1-to-
many relation defined in the EDMX file. When I obtain the list of
attendees for a given meeting as myMeeting.Attendees, they come from a
SQL join between the tblMeeting and tblAttendee tables, which Entity
Framework figures out. However, I would like to use a custom stored
proc that lists Meetings along with their Attendees which are computed
using some logic in the stored proc. I would like to use a temp table
in the sproc and apply various other intermediate computations to
arrive at the meetings and their associated attendees. I want to
filter out attendees linked to meetings at the DB layer and not at the
business layer for performance reasons.
This is just one of the cases. There are several other entities for
which I need customized computation of related entities to be done at
the DB layer before returning the data to the business layer.

The dev experience could be like:
MyEntities ent = new MyEntities ();
IEnumerable<Meeting> meetings = ent.MeetingSet.Include
("Attendees").UseSproc("spLoadMeetings").AsEnumerable<Meeting>();

foreach(Meeting myMeeting in meetings)
{
foreach(Attendee a in myMeeting.Attendess)
{
//My code
}
}


Is this supported in Entity Framework? Is it part of EF 4.0?

Looking at your issue, it looks like at least some of what you are
looking at is not supported until EF 4.0 (at least not out of the box).
There is some support for sprocs right now, and you can link the results
to your EF model today. The lazy loading is heading into EF 4.0 and if
you want POCO (plain old CLR objects) as your models, you are definitely
EF 4.0.

The full LINQ experience in EF 4.0 (which will run on the data layer
despite appearing to be business layer, at least in the "normal"
scenarios) may be at least part of what you are looking for.


--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************