Lazy loading not working in EntityFramework 6

Question

I have made the below properties as true.

Configuration.LazyLoadingEnabled = true;
Configuration.ProxyCreationEnabled = true;

I have two classes :

 public partial class User : BaseEntity
    {
        public User()
        {
            this.UserRoles = new List<UserRole>();
        }

        public int UserId { get; set; }
        public string FirstName { get; set; }
        public virtual ICollection<UserRole> UserRoles { get; set; }
    }

public partial class UserRole : BaseEntity
    {
        public int UserRoleId { get; set; }
        public int UserId { get; set; }
        public int RoleId { get; set; }
        public virtual User User { get; set; }
    }

I am calling a stored procedure from EF 6 like this :

public IEnumerable<User> GetUserDetails(int? userId)
{
    var userIDParameter = userId.HasValue ?
    new SqlParameter("@UserId", userId) :
    new SqlParameter("@UserId", typeof(int));
    return _dbContext.Database
        .SqlQuery<User>("usp_GetUserDetails @UserId", userIDParameter);
}

Calling in Controller like this :

IEnumerable<User> user =  _storedProcedureService.GetUserDetails(5);

In my SP I have logic as :

Select * from User where UserId = @UserId

I get all the data related to User but UserRoles.Count comes as zero.

What I am doing wrong ?

I revoked my comment as this SO thread says it is possible stackoverflow.com/questions/2372288/… — Jenish Rabadiya
– Jenish Rabadiya, Commented Apr 7, 2015 at 14:19

Dion V. · Accepted Answer · 2015-04-07 14:26:59Z

4

You are asking about using Lazy Loading with Entity Framework while you are using ADO.NET. ADO.NET does not support Lazy Loading, Entity Framework does.

For your info, this is ADO.NET;

var userIDParameter = userId.HasValue ?
                new SqlParameter("@UserId", userId) :
                new SqlParameter("@UserId", typeof(int));
return  _dbContext.Database.SqlQuery<User>("usp_GetUserDetails @UserId", userIDParameter);

Which uses SqlParameter and stuff like that.

This is Entity Framework;

using (var db = new MyContext())
{
    return db.User.Single(u => u.UserId = id);
}

Or, with stored procedures;

using (var db = new MyContext())
{
    return db.GetUserDetails(id);
}

If you want to let Entity Framework handle stored procedures and support Lazy Loading, I suggest you follow this tutorial.

edited Apr 7, 2015 at 14:26

answered Apr 7, 2015 at 14:15

Dion V.

2,1202 gold badges14 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Bokambo Over a year ago

The Database class is defined in System.Data.Entity which comes from EF. How it is related to ADO.Net ? In EF approach what you showed you are not using Stored Procedure, i need to use SP.

Dion V. Over a year ago

@user662285 When using SqlQuery<>(), you create raw queries which are not tracked by the context and thus are not lazy loaded. I suggest you also read this.

Gert Arnold Over a year ago

Whatever. It's not true that this is pure ADO.net. Database.SqlQuery<User> even creates dynamic proxies. They just don't lazy load, which other proxies from DbSets do (even when not tracked). I'm not entirely sure, but I think this worked in previous EF versions, so this may be a bug or a breaking change.

Dion V. Over a year ago

@GertArnold Stored procedures work with lazy loading, just not the way the OP has implemented it. Perhaps you should also read the tutorial?

anaximander Over a year ago

The choise to have DbContext expose Database as a public property has caused so much damage and confusion... the temptation for developers familiar with ADO.NET and its SqlQuery has led many astray.

Gert Arnold · Accepted Answer · 2015-04-10 10:29:02Z

The answers so far completely miss the mark. It's incorrect to say that an Entity Framework method (context.Database.SqlQuery<T>) bypasses Entity Framework or that it's pure ADO.Net.

You use SqlQuery<T>. This is different than the database-first approach, in which a stored procedure is imported into an EDMX. An imported stored procedure is executed by a different method: ExecuteFunction. When this method is called and the result maps to an entity type, two things happen that don't happen when context.Database.SqlQuery<T> is executed.

The entities are tracked by the context's change tracker.
The entities perform lazy loading.

OK, two different methods present different behavior, why not? I could live with that. The only thing (and the answers overlook this fact) is that context.Database.SqlQuery<T> returns dynamic proxies when T is an entity type. A dynamic proxy is expected to perform lazy loading. That's one of the things it's made for. So to me it's unexpected that these two statements don't produce the same result:

var roles1 = _dbContext.Database.SqlQuery<User>(...).First().UserRoles;
var roles2 = _dbContext.Users.AsNoTracking().First(u => u.UserId == x).UserRoles;

Only the second statement performs lazy loading, but both create a proxy User entity. Maybe this is by design. Even so, it is confusing.

J.W. · Accepted Answer · 2015-04-07 14:09:44Z

0

You are using stored procedures to retrieve your user objects which bypass all the Entity Framework , you will need use Entity Framework to retrieve your user to have Entity Framework do its job. Something like the following.

_context.Set<Users>().Single(u=>u.UserId=5)

answered Apr 7, 2015 at 14:09

J.W.

18.2k7 gold badges47 silver badges76 bronze badges

Comments

DanoB · Accepted Answer · 2015-11-14 14:23:46Z

0

The original post is correct. SQLQuery is an EF method but it doesn't return an object that supports lazy loading. Since it uses reflection to return the selected columns into the object the object key might not even be set. In this case Child objects can't be identified. The object returned can't be used like object.Parent.ID or object.Children.Count().

answered Nov 14, 2015 at 14:23

DanoB

1011 silver badge3 bronze badges

Collectives™ on Stack Overflow

Lazy loading not working in EntityFramework 6

4 Answers 4

5 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related