0

I have a collection of data 

List<Data> data = new List<Data>(){
      new Data() {
         Column1 = 50,
         Column2 = "48148148",
         Column3 = "1414141414"
      }
}

I can have a thousand records like this in that collection.

Now I have to see if anything in that collection already exists in my database, if so bring back the record to be updated.

I don't want to do a 1000 sql statements all with 

 where Column1= @param1 and Column2 = @param2 and Column3 = @param3

I really want to do 1 query to get all the records back.

Is there some way I can take my collection I have and built a join statement(maybe an inner join) with it or something.

I seem to remember seeing something where you can do a "select join" or something like that but I can't really remember it totally.

chobo2
  • 83,322
  • 195
  • 530
  • 832
  • you could do a search on how to do a `in` statement in linq. - hint `exists`... – jazb Oct 31 '18 at 05:25
  • I am not following are you saying when you say in statement are you referring I would use this exists with entity framework? – chobo2 Oct 31 '18 at 05:44
  • sorry - my mistake - i meant `contains` - have a look at this post: https://stackoverflow.com/questions/1071032/searching-if-value-exists-in-a-list-of-objects-using-linq – jazb Oct 31 '18 at 05:46
  • 1
    Read [my answer to your previous post](https://stackoverflow.com/a/53058698/3094533) - it shows you how to use a table valued parameter for upsert - you can use a table valued parameter for select as well. – Zohar Peled Oct 31 '18 at 08:28
  • @ZoharPeled - Yes i saw your answer, but I wanted to see if there are other ways possible as well as I just really just don't like SP, also I still don't understand all the code you posted but have not had enough time to research it before I ask questions. Probably in a few days I will be posting up questions. – chobo2 Oct 31 '18 at 16:30
  • @JohnB - I dont' know how "contains" would help in my situation. If I make 3 contains for each of the columns and send all 1000 records data in, won't it start matching stuff that it should not? Maybe I am missing something – chobo2 Oct 31 '18 at 16:31
  • You can use also table valued parameters in "inline" queries - but I really don't see the benefit here. – Zohar Peled Oct 31 '18 at 16:34
  • LINQ to SQL parameter passing for `Contains` is limited to 2100 parameters, so you would need to chunk the queries in 2000/(# of collection props) chunks to work (in this case, 2000/3 or about 667 items). Or you could load your collection into a temp table (or use a query table) in SQL and then join against that. – NetMage Oct 31 '18 at 20:41
  • PS You would use `Contains` like `where data.Contains(new Data { Column1 = table.Column1, Column2 = table.Column2, Column3 = table.Column3 })` - are you using LINQ? – NetMage Oct 31 '18 at 20:44
  • @NetMage - I not following. Getting the properties out of data is not really the problem, the problem is taking those parameters out and using them to build query in EF Core or Ado.net based on parameters in data collection. – chobo2 Oct 31 '18 at 21:46
  • See my previous comment - you can't use a single parameterized query for 1000x3 properties as the limit is 2100 parameters. You can either use a temp table or chunk the queries. – NetMage Nov 01 '18 at 20:07
  • You said Linq to SQL can't but I can do ado.net if that is the issue unless it happens there are well. I am still not sure if I do 3 contains though if it will lead to unexpected tables. Would this temp table be made in memory or in the database? – chobo2 Nov 01 '18 at 23:48

0 Answers0