CODEWITHSUNDEEP
c#.netlinqlinq-to-sql
areller
areller

Reputation: 5238

LINQ to SQL entity to object conversion

As you know, LINQ to SQL generates its own entities. I try to fetch data from the database and put it in my own entities, classes that I create.

And when I do it like this:

        AppData.MyDBDataContext context = new AppData.MyDBDataContext();
        List<User> users = (from user in context.Users.Select(x => new User
        {
            Id = x.Id,
            Name = x.Name,
            Password = x.Password
        }) where user.Password == "123456" select user).ToList();

It works (User is a class that I created). But when I try to generalize and build a casting function:

User castToUser (DataAccess.AppData.User linqEntity)
{
    return new User
    {
        Id = linqEntity.Id,
        Name = linqEntity.Name,
        Password = linqEntity.Password
    };
}

(DataAccess.AppData.User is the LINQ to SQL generated entity).

    AppData.MyDBDataContext context = new AppData.MyDBDataContext();
    List<User> users = (from user in context.Users.Select(x => castToUser(x)) where user.Password == "123456" select user).ToList();

It throws an error: Method 'DataAccess.Entities.User castToUser(DataAccess.AppData.User)' has no supported translation to SQL.

The error appears just from changing

new User
            {
                Id = x.Id,
                Name = x.Name,
                Password = x.Password
            }

to castToUser(x), which returns the same thing!

Upvotes: 0

Views: 138

Answers (1)

Wiktor Zychla
Wiktor Zychla

Reputation: 48314

This happens because l2sql tries to come up with a translation of your projection. Instead, just make it do the projection after the data is retrieved:

AppData.MyDBDataContext context = new AppData.MyDBDataContext();
List<User> users = context.Users
   .Where( u => u.Password == "123456" )
   .ToList() // fetch
   .Select( u => castToUser( u ));

An advice - don't mix the two styles, the linq style and the extension method style. This lowers the readability.

Upvotes: 5

Related Questions