Entity Framework - correctly load a graphic object using stored procedures

Question

Background

I am changing my LINQ-to-SQL code in my Entity Framework project. Most of the changes were relatively simple, but I ran into a pretty serious problem. With LINQ-to-SQL, I was able to load the entire object graph (for model B) using stored procedures:

ViewModel.Model = MyDbContext.usp_ModelA_GetByID(AId).Single();
List<ModelB> Details = 
    (from b in MyDbContext.usp_ModelB_GetByID(BId)
    join c in MyDbContext.usp_ModelC_GetAll()
       on b.CId equals c.CId
    select new ModelB()
    {
        BId = b.BId,
        CId = b.CId,
        C = c
    }).ToList();
ViewModel.Model.ModelBs.AddRange(Details);

, EF, , ViewModel.Model.ModelBs, "EntityCommandExecutionException" , , " " SELECT " " ModelBTable "." , EF ModelB ModelA, . , , , , , , , , , , "".

EF, , :

ViewModel.Model = MyDbContext.usp_ModelA_GetByID(AId).Single();
List<ModelB> Details = 
    (from b in MyDbContext.usp_ModelB_GetByID(BId)
    join c in MyDbContext.usp_ModelC_GetAll()
        on b.CId equals c.CId
     select new ModelB()
     {
        BId = b.BId,
        CId = c.CId,
        C = c
     }).ToList();
ViewModel.Model.ModelBs = new EntityCollection<ModelB>();
foreach (ModelB detail in Details)
{
    ViewModel.Model.ModelBs.Attach(detail);
}

"InvalidOperationException" "EntityCollection , , EntityCollection, ObjectContext. InitializeRelatedCollection EntityCollection .".

, , , . ORM .

, , , EF, , , :

ViewModel.Model = 
    (from a in MyDbContext.usp_ModelA_GetByID(AId)
    select new A()
    {
        AId = a.AId,
        ModelBs = (from b in MyDbContext.usp_ModelB_GetByID(BId)
                  join c in MyDbContext.usp_ModelC_GetAll()
                      on b.CId equals c.CId
                  select new ModelB()
                  {
                      BId = b.BId,
                      CId = b.CId,
                      C = c
                  }).ToEntityCollection()
    }).Single();

ToEntityCollection , :

public static EntityCollection<TEntity> ToEntityCollection<TEntity>(
     this IEnumerable<TEntity> source) where TEntity : class, IEntityWithRelationships
{
    EntityCollection<TEntity> set = new EntityCollection<TEntity>();
    foreach (TEntity entity in source)
    {
        set.Attach(entity);
    }
    return set;
}

"InvalidOperationException" " , End null. , , .".

, , .

, , : , , Entity Framework 4?

, , , :

• , . . , , , , .

• edmx, . , edmx , .

2

, . , , ViewModel, List ModelBs, , , , , . , , ViewModel , ViewModel, ModelA, ModelB, ! , :

(from b in MyDbContext.usp_ModelB_GetByID(BId)
join c in MyDbContext.usp_ModelC_GetAll()
    on b.CId equals c.CId
select new ModelB()
{
    BId = b.BId,
    CId = b.CId,
    C = c //<------Setting a navigation property and EF figures out that it belongs
}).ToList();

:

(from a in MyDbContext.usp_ModelA_GetByID(AId)
select new ModelA()
{
    AId = a.AId,
    ModelBs = MyDbContext.usp_ModelB_GetByID(BId).ToEntityCollection() //<----Won't let me set the navigation property when the navigation property is a collection.
}).Single();

Answer 1

, , , , , . - , EnableLazyLoading false DbContext. , EF, , :

ViewModel.Model = MyDbContext.usp_ModelA_GetByID(AId).Single();
var Details = 
(from b in MyDbContext.usp_ModelB_GetByID(BId)
join c in MyDbContext.usp_ModelC_GetAll()
   on b.CId equals c.CId
select new ModelB()
{
    BId = b.BId,
    CId = b.CId,
    C = c
}).ToList();  
//ToList() executes the proc and projects the plate details into the object 
//graph which never tries to select from the database because LazyLoadingEnabled is
//false.  Then, the magical relationship fix-up allows me to traverse my object graph
//using ViewModel.Model.ModelBs which returns all of the ModelBs loaded into the graph
//that are related to my ModelA.

Answer 2

, . MSDN , , .

:

EntityB proc:

create proc dbo.Get_EntityB_by_EntityAId( @aId int )
as

select distinct
    b.EntityBId
    , b.Description
from
    EntityA a
    left outer join EntityB b
     on a.PrimaryEntityB_EntityBId = b.EntityBId
    left outer join EntityB b2
     on a.AlternativeEntityB_EntityBId = b2.EntityBId
where
    a.EntityAId = @aId
go

EntityA proc ( B proc)

create proc dbo.Get_EntityA_by_Id( @id int )
as

-- use a select statement
select 
    a.EntityAId
    , a.Description
    , a.PrimaryEntityB_EntityBId
    , a.AlternativeEntityB_EntityBId
from
    EntityA a
where
    a.EntityAId = @id

-- and/or other sprocs
exec dbo.Get_EntityB_by_EntityAId @id

go

[Table("EntityA")]
public partial class EntityA
{
    public int EntityAId { get; set; }
    public string Description { get; set; }


    public virtual EntityB PrimaryEntityB { get; set; }

    public virtual EntityB AlternativeEntityB { get; set; }
}


[Table("EntityB")]
public partial class EntityB
{
    public int EntityBId { get; set; }
    public string Description { get; set; }

    [InverseProperty("PrimaryEntityB")]
    public virtual ICollection<EntityA> EntityAsViaPrimary { get; set; }
    [InverseProperty( "AlternativeEntityB" )]
    public virtual ICollection<EntityA> EntityAsViaAlternative { get; set; }
}

, sproc ( EntityA, )

public static void EagerLoadEntityA( int aId )
{
    using( var db = new TestEntities() )
    {
        // if using code first
        db.Database.Initialize( false );

        var cmd = db.Database.Connection.CreateCommand();
        cmd.CommandText = "dbo.Get_EntityA_by_Id";

        db.Database.Connection.Open();

        try
        {
            var reader = cmd.ExecuteReader();

            var objContext = ( ( IObjectContextAdapter )db ).ObjectContext;

            var aEntities = objContext
                .Translate<EntityA>( reader, "EntityAs", MergeOption.AppendOnly );

            reader.NextResult();

            var bEntities = objContext
                .Translate<EntityB>( reader, "EntityBs", MergeOption.AppendOnly );

        }
        finally
        {
            db.Database.Connection.Close();
        }
    }
}

:

EagerLoadEntityA( 1234 );
var entityA = db.EntityAs.Find( 1234 ); // cached
var primB = entityA.PrimaryEntityB; // this is already loaded

Answer 3

/ . , , , . , EF Entity Insert, Update Delete.

EF . http://msdn.microsoft.com/en-us/data/gg699321.aspx