How to use factory classes with linq for sql?

Question

Hi,

I have a model on top of my database model and map the objects in my Repository.

However, apparently it makes a difference whether I "select new" directly in my GetUsers or "select factoryresult" as implemented below. I get the error at runtime, that the method CreateFromDbModel does not have a translation to sql (System.NotSupportedException).

Is there a way around this? Can I mend it somehow?

The reason for wanting to use the factory method is that I might instanciate objects elsewhere and want to keep the 'mapping code' in one place...

Thanks for any comments, Anders

    public IQueryable<User> GetUsers(bool includeTeams)
    {
        return from u in _db.sc_Players
               where (includeTeams || (!u.aspnet_User.sc_Player.IsTeam))
               select UserFactory2.CreateFromDbModel(u);
    }


    public static User CreateFromDbModel(sc_Player player)
    {
        return new User
                   {
                       Id = player.sc_PlayerID,
                       FirstName = player.FirstName.Trim(),
                       LastName = player.LastName.Trim(),
                       PresentationName = player.FirstName.Trim() + " " + player.LastName.Trim(),
                       LoginName = player.aspnet_User.LoweredUserName,
                       IsTeam = player.IsTeam,
                       Email = player.aspnet_User.aspnet_Membership.Email,
                       Password = player.aspnet_User.aspnet_Membership.Password
                   };
    }

Answer 1

Is the problem becuase you are returning IQueryable in your GetUsers method? Try returning List instead. This will force the Linq query to execute within the method.

public List<User> GetUsers(bool includeTeams)
{
    return (from u in _db.sc_Players
    where (includeTeams || (!u.aspnet_User.sc_Player.IsTeam))
    select UserFactory2.CreateFromDbModel(u)).ToList();
}

Not sure if this will fix the problem, just a hunch. I was able to duplicate what you are doing in Linqpad on a local database, and it worked. But my sample was not returning an IQueryable. Are you furthur modifing the IQueryable collection outside of GetUsers()?

Edit:

I've done some more checking. I was able to duplicate the error only when I modified my sample so the IQueryable collection was used in a second Linq query after calling GetUsers():

IQueryable<User> query = GetUsers(true);

var q = from u in query
    where u.Name.Contains("Bob")
    select new {Name = u.FirstName + " " + u.LastName};

I bet if you will return a List as suggested above in GetUsers() the error will go away. The only down side is that any filtering you do after calling GetUsers() will not limit the amount of data returned from the database because you have already executed the query when calling .ToList().

Edit 2:

Unfortunately I don’t think there is any other way to include your factory method in the query. I do have one more idea. You can create an extension method for IQueryable, call it something like ToUserList(). Inside ToUserList() you call ToList() on the query and your factory method that returns a collection of users. Call this method when you are finished filtering the data using Linq. This will allow you to only execute the query when you are ready to load the data from the database. An example is given below.

public static List<Users> ToUserList(this IQueryable<User> query)
{
     return query.ToList().Select(u => UserFactory2.CreateFromDbModel(u)); 
}

Call the extension method like this:

// Filter the data using linq. When you are ready to execute the query call:
query.ToUserList(); // Query will execute and a list of User objects returned.

Hope this makes sense. Douglas H.

Answer 2

The error pretty much explains it all.

"method CreateFromDbModel does not have a translation to sql (System.NotSupportedException)"

Your CreateFromDbModel method isn't an sql function. Your application won't be able to run the CreateFromDbModel function until the objects are specifically returned to you from the server. You'll most likely have to call a ToList() or something similar on your query before you can run CreateFromDbModel on them.