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

Question

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?

Dan Diplo · Accepted Answer · 2010-08-12 18:53:11Z

6

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));

answered Aug 12, 2010 at 18:53

Dan Diplo

25.4k4 gold badges71 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Micael Bergeron · Accepted Answer · 2010-08-12 19:24:22Z

3

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}

answered Aug 12, 2010 at 19:24

Micael Bergeron

1,1847 silver badges13 bronze badges

1 Comment

msarchet Over a year ago

Then you could have public class Unicorn : Pet {...}

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related