Searching through multiple tables at once (Linq to SQL)?

I have a couple tables that are kind of unrelated - id like to search through both of them and create a type that i can sift through 开发者_开发知识库later

something like this doesnt work

var results = from dog in _dataContext.Dogs  
                      where dog.Name.Contains(search)  

                      from catin _dataContext.Cats  
                      where cat.Name.Contains(search)  

                      select new AnimalSearchResults  
                                  {  
                                      Dog = dog,  
                                      Cat = cat  
                                  };  

        return results;  

I basically want to create a list of "AnimalSearchResults" that contains all dogs and all cats that have that name

Whats the best way to do something like this?

Sounds like you want to Union the two results so your basic query would be something like:

var results = (from dog in _dataContext.Dogs  
                      where dog.Name.Contains(search))
                      .Union
                      (from cat in _dataContext.Cats  
                      where cat.Name.Contains(search));

The other workaround would be to have the class Cat and the class Dog derivate the same base class, which would then carry the name.

Here's an example

public class Pet
{
    public string Name {get; set;}
}

public class Dog : Pet {...} 
public class Cat : Pet {...}
// I assume each classes have their singularity;

In your DataContext Object, create a GetPets() method, like this one :

public IEnumerable<Pet> GetPets()
{
    return Cats.Cast().Union( Dogs.Cast() );
}

Then use LINQ on the Pets in the DataContext.

var results =
    (from p in _dataContext.GetPets()
     where p.Name.Contains(search)
     select p);

This code would be more scalable, especially if u have more than 2 derivated classes.

{enjoy}