What causes the Linq error: This method cannot be translated into a store expression?

Question

I have a bunch of Linq to Entity methods that had the same select statement, so I thought I would be clever and separate that out into it's own method to reduce redundancy... but when i attempted to run the code, i got the following error...

this method cannot be translated into a store expression

Here is the method i created...

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

And am calling in a method like this...

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: here is the code that works inline

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;
            }
        }

Answer 1

This expression will work to give the desired result (somewhat) I still havent figured out how to pass in additional variables in teh select statements...

  ..... .Select(GetUser).FirstOrDefault()        

static readonly Expression<Func<DbUser, User>> GetUser = (g) => new User {
            Uid = g.uid,
            FirstName = g.first_name,
            LastName = g.last_name,
            BigPicUrl = g.pic_big,
            Birthday = g.birthday,
            SmallPicUrl = g.pic_small,
            SquarePicUrl = g.pic_square,
            Locale = g.locale.Trim(),
            //IsFavorite = g.FavoriteFriends1.Any(x=>x.uid==uid),
            FavoriteFriendCount = g.FavoriteFriends.Count,
            LastWishlistUpdate = g.WishListItems.OrderByDescending( x=>x.added ).FirstOrDefault().added
        };

Answer 2

The error is spot on, you can't translate that into a T-SQL (or P-SQL) query.

You need to make sure you've executed the query before you attempt to hydrate it into some other type.

Keep it simple, use an extension method. That's what they are there for.

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

Then 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 after the context has been disposed? This way, you ensure EF has finished it's expression work before you attempt to hydrate into a custom object.

Also i dont know why you're passing uid to that method, it's not even being used.

On a further note, you shouldn't need to do this kind of thing (project EF POCO's into your own objects).

If you do, it's a good case for custom POCO's (map the tables straight into your custom POCO's, don't use the Code Generation).

Answer 3

You can't do this because the getUser method cannot be converted to any TSQL statement. if you return your DBUser first and then use it as the first parameter of the GetUser method then you are forcing it to execute and once you have you DBUser you can pass it to GetUser

Maybe you can try this:

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

EDIT

Since you are saying it still fails could it be beacuse of the enum??

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