CODEWITHSUNDEEP
c#linqt-sql
jglouie
jglouie

Reputation: 12880

Filtering SQL Table results based upon C# collection

I have a simple table that basically has a NVARCHAR Name column and a DateTime Timestamp column. I want to write a C# function that takes a collection of Names and DateTimes, and return everything from my table where the Names match and the DateTimes in the collection are older than the DateTimes in the table.

The simple way my non-SQL brain came up with is to create a SELECT statement to get all of the matching Names (“SELECT Name,Timestamp From MyTable WHERE Name IN (...)”) and then iterate through the returned collection comparing the DateTimes.

There has to be a better way to do this with SQL or Linq. How can I more efficiently do this?

UPDATE: Thanks for the helpful feedback! Sounds like running the query analyzer on the various approaches listed here will be the way to go. Here's another one to throw out there, if any are curious:

List<Item> items = ... // this the collection
var db = new ItemDataContext(); // From .Net EF

  string[] results = (from item in items
                      from dbItem in db.Items
                      where item.Name.Equals(dbItem.Name,StringComparison.InvariantCultureIgnoreCase)
                            && item.Timestamp < dbItem.Timestamp
                      select dbItem.Token).ToArray();

Upvotes: 0

Views: 966

Answers (4)

Moe Sisko
Moe Sisko

Reputation: 12013

If you are using SQLServer 2008, another alternative might be to pass a .net DataTable to TSQL. For more info : http://www.sommarskog.se/arrays-in-sql-2008.html#DataTable

Upvotes: 1

faester
faester

Reputation: 15086

Using a large IN list is not neccessarily bad, but you may get into problems if you have thousand of elements in the list since the numnber of parameters may cause problems.

But basically the other approach would be to join on some (temporary) table. Using that strategy would however require you to insert the data into the table, so unless you can reuse the query many times the work involved with inserting the filter data would largely exceed the work in returning the results.

The best advice here as anywhere else would be to look at the query analyzer for results though.

An example of the second approach using a temporary table is shown below. Here you may get the benefit of using indexes in the JOIN, but this should be contrasted to the cost of the inserts for each filter value and the complexity in the code utilizing the filter function. (A simple IN clause is autogenerated by LINQ, while the temp table approach would require quite some plumming in the code.)

   CREATE TABLE vals (v nvarchar(255), inserted DATETIME DEFAULT(getdate()))

   SELECT * FROM vals 
     WHERE v IN ('a', 'aa', 'aaa')

   DECLARE @filters TABLE (v varchar(256))

   INSERT INTO @filters (v) VALUES ('a')
   INSERT INTO @filters (v) VALUES ('aa')
   INSERT INTO @filters (v) VALUES ('aaa')

   SELECT * FROM vals INNER JOIN @filters f
     ON f.v = vals.v

Upvotes: 1

Jonathan
Jonathan

Reputation: 1892

Given a list of some type that contains string and datetime:

public class MyType
{ 
    public Name {get;set;} 
    public Timestamp {get;set;}
}

SPROC search results populates a List<MyType> myList You're also given a list of strings List<string> myNamesList

var results = myList.Where(r => myNamesList.Contains(r.Name) && myList.Timestamp > someDateTime).ToList();

Results in a List<MyType> of your filtered results.

Upvotes: 1

JBrooks
JBrooks

Reputation: 10013

I have a blog post about passing in a list of IDs here: Passing IDs to Stored Procs Using XML

Ends up looking like:

EXECUTE dbo.getLocationTypes '<IDList><ID>1</ID><ID>3</ID></IDList>', 
'<IDList><ID>200</ID><ID>300</ID><ID>400</ID></IDList>'

Upvotes: 2

Related Questions