Materilizing related entities from SPs using EF Extensions
I have a stored procedure that returns a collection of m开发者_开发百科y entity class objects. Since I want my navigation properties populated as well I'm using EF Extensions and I've written my own Materializer
class.
But. My entity class has a navigation property of Type
that points to a different entity. Stored procedure of course returns an ID from the lookup table (foreign key).
I would like to populate my types as if I was eagerly loading a related entity. How do I do that in my Materializer? Can I do it without having a stored procedure that returns two result sets?
I would like to implement something similar to what Include()
extension method does on source selection in LINQ statement.
I've solved this myself some time ago. I'm just answering my own question if anyone else would need it.
So what kind of results should stored procedures return? It hugely depends on relation type. Lets say we have two tables: TableOne
and TableTwo
.
1:0..1 relation
In this case stored procedure should return both at once:
select t1.*, t2.*
from TableOne t1
[left] join TableTwo t2
on t2.key = t1.key
When they are 1:1, you can easily omit left
.
1:MANY relation
In this case it's much easier to write stored procedures that return more results. Starting from those at side one so they will be prepared when you bind the many side table.
/* relation one */
select *
from TableOne
/* relation many */
select *
from TableTwo
But if you would still like to return a single result set, you should check for each record whether you already have a certain entity loaded. There's a method called FindOrAttach() that can help you. So each result will return both entities and you have to chech if they are loaded. If they are not, materialize... But as mentioned: it's much easier to return two result sets.
MANY:MANY relation
You should also write your stored procedure to return more results. In this case 3 of them.
/* first table */
select *
from TableOne
/* second table */
select *
from TableTwo
/* relation *:* */
select *
from TableOne2Table2
Materilize first two tables as usual and then call Attach for each record from thirs result loading by keys from TableOne and TableTwo. This will also populate entity set navigation properties.
I home this will help others as it helped me.
精彩评论