2

I've been looking up how to do this, and I found something close, but not quite what I'm looking for. I wonder if this might help others as well, but I could really use the help. I've got a pretty simple SELECT statement I need to convert into LINQ to SQL to speed up searches in our software:

SELECT  Animals.*
FROM    Animals 
        INNER JOIN AnimalAliases
        ON  Animals.AnimalID = AnimalAliases.AnimalID 
        AND AnimalAliases.Alias LIKE N'%USERINPUT%';

Basically, I want to be able to do a Inner join with multiple conditions, but one of the conditions has nothing to do with one of the tables, the LIKE statement, which is where I get stuck.

var query = 
    from animal in context.Animals
    join animalAlias in context.AnimalAliases
        on new { animal.AnimalID, "USERINPUT" }
    equals new { animalAlias.AnimalID, animalAlias.Alias }
    select animal;

but that doesn't work obviously, because I dont want EQUALS to USERINPUT, I want to perform various LIKE operations on it..

Anyone have any insight?

Steven
  • 166,672
  • 24
  • 332
  • 435
Philippe
  • 21
  • 1
  • 2

2 Answers2

3

I think what you're looking for is just a normal WHERE clause?

var query = from animal in context.Animals
   join animalAlias in context.AnimalAliases on animal.AnimalID equals animalAlias.AnimalID
   where animalAlias.Alias.Contains(USERINPUT)
   select animal;

The alias text is not part of the foreign key relationship - so it shouldn't be in the join.

Update - after comments - including from @Steven - while the query above is correct and does mimic the original SQL - it might be wise to use Distinct() on the output - this will prevent the case where multiple alias matches could occur for a single animal.

var query = (from animal in context.Animals
   join animalAlias in context.AnimalAliases on animal.AnimalID equals animalAlias.AnimalID
   where animalAlias.Alias.Contains(USERINPUT)
   select animal).Distinct();
Stuart
  • 66,722
  • 7
  • 114
  • 165
  • Ah I just wrote that one in too. Thanks for the help this has improved search speeds on animal aliases by hundreds of percents i'm sure on large databases. Before the way I was doing it was terrible and involved all animals all the time. – Philippe May 30 '11 at 15:15
  • 1
    Please note that your query can return duplicates. You should call `.Distinct()` over it to get unique values. – Steven May 30 '11 at 15:25
  • @Steven - Good point - although that is actually what the original SQL does too – Stuart May 30 '11 at 16:19
  • Wow... you're absolutely right about that. I don't think that it is the actual behavior the OP is expecting, but nonetheless very sharp. + 1 for that! – Steven May 30 '11 at 18:44
0

This will do the trick:

IQueryable<Animal> query = 
    from animal in context.Animals
    where animal.AnimalAliases
        .Any(a => a.Alias.Contains("USERINPUT"))
    select animal;

Alternatively, you can do it the other way around (start at the AnimalAlias entity instead of starting at the Animal entity):

IQueryable<Animal> query = (
    from animalAlias in context.AnimalAliases
    where animalAlias.Alias.Contains("USERINPUT")
    select animalAlias.Animal)
    .Distinct();
Steven
  • 166,672
  • 24
  • 332
  • 435
  • But it has to return a IQueryable set so I think the first one is the answer. I did actually figure it out another way, not sure if it helps anyone or if it even has validity, i haven't checked for accuracy: – Philippe May 30 '11 at 15:14
  • IQueryable query = from a in search.Animals join al in search.AnimalAliases on a.AnimalID equals al.AnimalID where al.Alias.Contains("USERINPUT") select a; – Philippe May 30 '11 at 15:15
  • Please stop using `joins` in linq. They obscure your code, and are in most situations unneeded, because the the O/RM knows about the relationships already. – Steven May 30 '11 at 15:22
  • @Philippe: Your query does not return a list of unique `Animal` entities. It can return duplicate `Animals`. btw. Both my queries return an `IQueryable`. They are functionally equivalent. – Steven May 30 '11 at 15:24
  • @Steven - I think it's good practice to use `join` in Linq - I agree for Linq2Sql the produced SQL will probably have the same performance, but when you move to Linq2Objects or to other ORMs then you might see a performance improvement (see JS's answer in http://stackoverflow.com/questions/3014123/linq-using-where-or-join-performance-difference) - also, as for whether `join` makes the code more obscure or not, I guess that's down to personal opinion (and people will always disagree on some things!) – Stuart May 30 '11 at 16:29
  • @Stuart: Over the last few years I've gotten pretty good in optimizing LINQ queries for performance, if I may say myself. However, there is one rule I always keep: I only optimize when there is a problem. In all other cases I write the most readable code. Using `join` seems like premature optimization to me. If you use a O/RM that screws up, it’s not a very good O/RM. It's hard for me to imagine that there is anyone who finds `join` more readable than the 'dotted' approach, but you're right, it's hard to argue about taste. – Steven May 30 '11 at 18:51