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

Question

I have several tables that are kind of unrelated - id, like to search on both of them, and create a type that I can sift through

something like this doesn't 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 with that name

What is the best way to do something like this?

Answer 1

Another workaround would be for the Cat class and Dog class to get the same base class, which then bears the name.

Here is 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 for pets in the DataContext.

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

, u .

{}

Answer 2

, , :

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