1

Let's say I have a database with 2 tables : User(Id, Name, PetId,...) and Pet(Id, Name, Color,...) and this class :

class UserPet
{
 // User table, generated in dbml
 public User User {get;}

 // Pet table, generated in dbml
 public Pet Pet {get;}

public UserPet(User user, Pet pet)
{
 User = user;
 Pet = pet;
}
 [...]
}

I'm trying to make a link to sql method with a filter like

public UserPet[] Get(Expression<Func<UserPet, bool>> criteria)
{
 [...]
// System.NotSupportedException: 'The member 'User' has not supported translation to sql'
 return (from user in context.User
        join pet in context.Pet on usr.PetId equal pet.Id
     -->   select new UserPet(user, pet).Where(criteria).ToArray()
}

So that, for instance, I can make query with dynamic filtering: 

Get(userPet => userPet.Pet.Color == "Red" && userPet.User.Name == "Dave")

Here, the criteria is an object containing 2 tables and I am struggling to use it in the linq to sql method. Any idea ? How to tell Linq To SQL that the properties of UserPet are the tables that were generated in the dbml ?

Thanks !

Lou
  • 277
  • 1
  • 5
  • 15
  • Why do you need a `UserPet` class? Does your `User` class have a `Pet` navigational property? – JuanR Jun 25 '18 at 18:40
  • Also, you mention `// Does not compile`. Why? What's the error? – JuanR Jun 25 '18 at 18:41
  • My bad, it is indeed compiling. I am going to edit the post. I have found a solution and I am going to write the response. Thanks ! – Lou Jun 26 '18 at 07:08

1 Answers1

0

After some time struggling, I found that the below modification worked (remove the constructor with parameter from UserPet). Why ? Mystery !

UserPet:

class UserPet
{
 public User user {get;set;}
 public Pet pet {get;set}
}

And method :

public UserPet[] Get(Expression<Func<UserPet, bool>> criteria)
{
 [...]
 return (from user in context.User
        join pet in context.Pet on usr.PetId equal pet.Id
        select new UserPet(){User = user, Pet = pet}).Where(criteria).ToArray()
}
Lou
  • 277
  • 1
  • 5
  • 15