What is the source of the Linq error: This procedure cannot be converted to a store expression?

.net c# entity-framework linq

Question

When I tried to execute the code, I saw the following issue. I had a lot of Linq to Entity methods that all had the same select statement, so I thought I would be smart and break that out into its own method to minimize repetition.

this method cannot be translated into a store expression

Here is the technique I developed.

public User GetUser(DbUser user, long uid)
{
    return new User
    {
        Uid = user.uid,
        FirstName = user.first_name,
        LastName = user.last_name
    };
}

And I'm calling this function in...

public User GetUser(long uid)
{
    using (var entities = new myEntities()) {
        return
            entities.DbUsers.Where( x => x.uid == uid && x.account_status == ( short )AccountStatus.Active ).
                Select( x => GetUser( x, uid ) ).FirstOrDefault( );
    }
}

UPDATE: This is the code for inline use.

public User GetUser(long uid, long uid_user)
        {
            using (var entities = new myEntities())
            {

                var q = from u in entities.DbUsers
                        where u.uid == uid_user
                        select new User
                        {
                            Uid = u.uid,
                            FirstName = u.first_name,
                            LastName = u.last_name,
                            BigPicUrl = u.pic_big,
                            Birthday = u.birthday,
                            SmallPicUrl = u.pic_small,
                            SquarePicUrl = u.pic_square,
                            Locale = u.locale.Trim(),
                            IsFavorite = u.FavoriteFriends1.Any(x => x.uid == uid),
                            FavoriteFriendCount = u.FavoriteFriends.Count,
                            LastWishlistUpdate = u.WishListItems.OrderByDescending(x => x.added).FirstOrDefault().added,
                            Sex = (UserSex)u.sex
                        };

                var user = q.FirstOrDefault();
                user.DaysUntilBirthday = user.Birthday.DaysUntilBirthday();
                return user;
            }
        }
1
6
2/27/2014 8:16:43 AM

Popular Answer

You cannot convert it into a T-SQL (or P-SQL) query, and that is the exact mistake.

Before attempting to hydrate the query into another type, you must first ensure that it has been run.

Use an extension approach and keep it basic. They are there to serve that purpose.

public static User ToUserEntity(this DbUser user)
{
    return new User
    {
        Uid = user.uid,
        FirstName = user.first_name,
        LastName = user.last_name
    };
}

Next, in your DAL:

public User GetUser(long uid)
{
    User dbUser;

    using (var entities = new myEntities())
    {
        dbUser = entities.DbUsers
                  .Where( x => x.uid == uid && x.account_status == (short)AccountStatus.Active )
                 .FirstOrDefault(); // query executed against DB
    }

    return dbUser.ToUserEntity();
}

See how I hydrate the POCO into an object that has been disposed of from the context? By doing this, you can make sure that EF has completed processing its expression before attempting to hydrate into a custom object.

Additionally, I'm not sure why you're supplying uid to that function because it isn't even utilized.

Additionally, you shouldn't project EF POCOs into your own objects. Zzz-37-Zzz.

If you do, it's an excellent reason to utilize custom POCOs (don't use code generation; map the tables directly into your own POCOs).

9
9/21/2010 5:18:59 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow