Linq query for EAV

Question

I have a big (4M records) table on a SQL Server with the following columns:

• Id (a record identifier);
• PropertyName (a string that represents a property name);
• PropertyValue (an integer that represent the property's value);

keep in mind that Id is not unique for this table. Basically, I'd like to write a LINQ query that retrieves Ids and all its associated PropertyName/PropertyValue pairs:

• Id
• dictionary (maybe?) PropertyName -> PropertyValue;

is this feasible in some way?

Thank you in advance

Answer 1

Not sure regarding performance but you can use GroupBy to group your data by similar Ids

Something like:

var result =
  from x in whatever
  group x by x.Id into g
  select new { 
         Id = g.Key, 
         Data = g.ToDictionary(i => i.PropertyName, i => i.PropertyValue)
  };

Refer to 101 LINQ Samples to learn more about Linq