Linq query with multiple records

 I have a question regarding LINQ query using lightspeed context. I have a list say:

List<int> currentObjIDs = new List<int> {200, 201, 202, 200};

Now I want to use above list for comparision and querying in the database. Something like below:

var

.LightSpeedWegDataContext.WbWegvakkens

where

select

Wegvak

{

 something...

 }

Now in above case, I am getting resultset with three objects but I want all four records (may be duplicate) in my resultset. Can anybody suggest a possible solution for that. I dont want to execute the query in loop.

Thanks,

That's not the semantics of Contains.  Contains takes the value in the database and checks if it belongs to the collection.  It doesn't perform a map from the collection.  For that you would need Select:

currentObjIDs.Select(id => dc.WbWegvakkens.Single(w => w.Id == id));

Unfortunately this will result in a separate database lookup for each entry in the collection, which would get expensive for large collections.  As far as I know there isn't a way to perform this mapping in a single SQL query; if there is, it isn't supported in LightSpeed.

The best you can do is probably perform the Contains query to efficiently get the entities, then use LINQ to Objects to map the list of IDs:

// Force the entities to load into the UOW in one SQL statement
dc.WbWegvakkens.Where(w => currentObjIDs.Contains(w.Id)).ToList();
// Use FindById to get the entities out of the identity map
return currentObjIDs.Select(id => dc.FindById<WbWegvakken>(id));

This is efficient because FindById checks the identity map first, so it will not perform a database query if the entity is already loaded (which we have ensured using the first line).  Exception: if your collection contains IDs which are not in the database, then these will not be returned by the initial query, so FindById will see that the ID is not in the identity map and will issue an unwanted database query.  If you are likely to have to process collections containing invalid IDs, you can use the collection returned by the initial query:

var entities = dc.WbWegvakkens.Where(w => currentObjIDs.Contains(w.Id)).ToList();
var lookup = entities.ToDictionary(e => e.Id);
var results = currentObjIDs.Select(id => SafeGetValue(lookup, id));

where SafeGetValue calls Dictionary.TryGetValue to return the lookup value or a suitable default if missing.

Get duplicate items from a list using LINQ

List duplicates = lst.GroupBy(x => x) .Where(g => g.Count() > 1) .Select(g => g.Key) .ToList();

More about...C# List

Mercal