424

I want to add some rows to a database using Linq to SQL, but I want to make a "custom check" before adding the rows to know if I must add, replace or ignore the incomming rows. I'd like to keep the trafic between the client and the DB server as low as possible and minimize the number of queries.

To do this, I want to fetch as little information as required for the validation, and only once at the beginning of the process.

I was thinking of doing something like this, but obviously, it doesn't work. Anyone have an idea?

Dictionary<int, DateTime> existingItems = 
    (from ObjType ot in TableObj
        select (new KeyValuePair<int, DateTime>(ot.Key, ot.TimeStamp))
    )

What I'd like to have at the end would be a Dictionary, without having to download the whole ObjectType objects from TableObject.

I also considered the following code, but I was trying to find a proper way:

List<int> keys = (from ObjType ot in TableObj orderby ot.Key select ot.Key).ToList<int>();
List<DateTime> values = (from ObjType ot in TableObj orderby ot.Key select ot.Value).ToList<int>();
Dictionary<int, DateTime> existingItems = new Dictionary<int, DateTime>(keys.Count);
for (int i = 0; i < keys.Count; i++)
{
    existingItems.Add(keys[i], values[i]);
}
nvoigt
  • 75,013
  • 26
  • 93
  • 142
Tipx
  • 7,367
  • 4
  • 37
  • 59

4 Answers4

751

Try using the ToDictionary method like so:

var dict = TableObj.Select( t => new { t.Key, t.TimeStamp } )
                   .ToDictionary( t => t.Key, t => t.TimeStamp );
nvoigt
  • 75,013
  • 26
  • 93
  • 142
tvanfosson
  • 524,688
  • 99
  • 697
  • 795
  • 3
    @pawan, it's a placeholder for each element in the enumeration and takes on the type of the objects in the enumeration. – tvanfosson Feb 08 '11 at 13:36
  • @ tvanfosson can you explain me it one. I am not able to Understand. Now my requirment is to Convert A Linq to Dictionary. Problem is Value is a String (creating in LINQ) format and key is p """var serverUrl = (from p in list[i].ProjectName select{ ("tcp://" + list[i].BuildMachineName + ":" + list[i].PortNumber+ "/CruiseManager.rem", p)}""" – PawanS Feb 08 '11 at 13:51
  • 1
    @pawan - that doesn't look right. I'd expect `var servers = list.Select( s => new { s.ProjectName, Url = "tcp://" + s.BuildMachineName + ":" + s.PortNumber + "/CruiseManager.rem" } ).ToDictionary( s => s.ProjectName, s.Url );` This creates a dictionary keyed by project name of project name/url pairs. – tvanfosson Feb 08 '11 at 14:30
  • 5
    Why is the `.Select( t => new { t.Key, t.TimeStamp } )` expression necessary? – Ben Collins Jul 11 '11 at 17:08
  • 15
    @BenCollins: I think the intermediate `.Select` causes the generated SQL to only select Key and TimeStamp, rather than selecting every column. – Joey Adams Jan 23 '14 at 16:01
  • 5
    You can omit this intermediary `Select` if you are doing Linq to Object (instead of Linq to SQL) – Pac0 Jan 18 '19 at 17:11
  • 1
    In case this helps someone, I was looking for this in the scope of EF Core 5.0. The intermediate `.Select` does avoid selecting every single column from an entity model/table in the database. – Giancarlo Sierra Jul 21 '21 at 20:45
133

Looking at your example, I think this is what you want:

var dict = TableObj.ToDictionary(t => t.Key, t=> t.TimeStamp);
tvanfosson
  • 524,688
  • 99
  • 697
  • 795
BFree
  • 102,548
  • 21
  • 159
  • 201
  • Wow... It might be simple as that... Since I'm pretty new to programming, I'll try that and do a little profiling to make sure that under the hood, I dont get the hit of the whole object. I'll keep you posted. – Tipx Jun 05 '09 at 02:10
  • 1
    I just made my check. Sadly, while getting the TableObj, it fetches all the objects from the db so I end up getting the trafic hit. I also checked the queries that the second way I posted (and wanted to avoid) and they only get the necessary items. Sure it does 2 queries so the server itself have to search twice the tables, but the object mapping is simple enough. – Tipx Jun 05 '09 at 02:20
  • 8
    You might be able to do: TableObj.Select(t => new { t.Key, t.TimeStamp }).ToDictionary(t => t.Key, t => t.TimeStamp); LinqToSql should be able to notice that you only want two things (from the select) and return them. I'm not sure it's smart enough to dig into the specifics of ToDictionary(). – Talljoe Jun 05 '09 at 02:25
  • 1
    NICE! Here is the resulting query : SELECT [t0].[Key], [t0].[TimeStamp] FROM [TableObj] AS [t0]. I don't want to take the credit for this so go ahead and post that as an anwser! :-P – Tipx Jun 05 '09 at 02:40
11

Try the following

Dictionary<int, DateTime> existingItems = 
    (from ObjType ot in TableObj).ToDictionary(x => x.Key);

Or the fully fledged type inferenced version 

var existingItems = TableObj.ToDictionary(x => x.Key);
JaredPar
  • 733,204
  • 149
  • 1,241
  • 1,454
  • Thanks for the anwser JaredPar. I taught about something like that, but I think this would return the whole objects of ObjType type, and Iwant to avoid having to download the whole objects. – Tipx Jun 05 '09 at 01:57
  • @Tipx, can you provide some information on what you want to filter on? Adding a filter clause is possible, but I can't tell from your question what's important – JaredPar Jun 05 '09 at 01:59
  • All I need to have to know if the "new row" need to be added, be ignored or replace another row is the timestamp of the object. The objects in the DB have plenty of fields I don't need for the validation and I don't want to get the performance hit of getting the whole objects. To keep it simple, I got a table in my BD with 20 columns, 100 000 rows and I'd want to extract a Dictionary using the values of the first 2 columns. – Tipx Jun 05 '09 at 02:04
  • I just checked the server queries generated by that code and as you probably knew, it gets the whole objects. – Tipx Jun 05 '09 at 02:21
1

Use namespace

using System.Collections.Specialized;

Make instance of DataContext Class

LinqToSqlDataContext dc = new LinqToSqlDataContext();

Use

OrderedDictionary dict = dc.TableName.ToDictionary(d => d.key, d => d.value);

In order to retrieve the values use namespace

   using System.Collections;

ICollection keyCollections = dict.Keys;
ICOllection valueCollections = dict.Values;

String[] myKeys = new String[dict.Count];
String[] myValues = new String[dict.Count];

keyCollections.CopyTo(myKeys,0);
valueCollections.CopyTo(myValues,0);

for(int i=0; i<dict.Count; i++)
{
Console.WriteLine("Key: " + myKeys[i] + "Value: " + myValues[i]);
}
Console.ReadKey();
Salman Mushtaq
  • 53
  • 13