IN Clause Best Practices? [solved]

[Dan Mos]

Hy, I have a problem in that I don't know what to choose. We have somewhere DataLayer/BussinessLogic something like:

public class MainClass{
.
.
.
protected ObservableCollection<SecondaryClass> lst;//contected to the main class through IDs in the DB
.
.
.
}

So the question is: What is the best way to get some rows based on a array/list of IDs from the "main" table: 1) Getting all the MainClass rows/objects than generating a string from code(c#) using StringBuilder with all the IDs separated by commas and run a query/storedproc to get all objects from the realted table(s) with a string/varchar paramter for the IN clause and fill the list of SecondaryClass objects from the MainClass or 2) Generate a temporary table with the IDs and use that in another query/proc as the IN or EXISTS filter? I've run some tests and for some reason or another from the speed point of view the first one is the winner. But both are pretty fast. So speed alone is not that big of an issue. Because of the Object Model we can not use a join. I mean we could but makes no sence. Any suggestions will be apreciated. Thanks

I bug

modified on Wednesday, July 14, 2010 1:47 PM

[Lost User]

Hi, Have you considered Table Valued Parameters[^]?

I are Troll :suss:

[Dan Mos]

:thumbsup: Nope. Nice solution. Thanks

I bug

[Lost User]

You're welcome :)