I am using Linq to Entities with EF and want an efficient way to do this. I what am doing is going through a list, counting distinct items in the list, appending the count on one element and return one string using String.Join. What I want (and I achieve) is this
One (3), Two(1), Three(2)
From a List having the items like this
One, Three, One, Three, Two, One
This would have been simpler had I not been retrieving from my POCO classes and processing all this on the fly for each entry in my db and passing the List to my DataGridView.
My code is like this,
public class Module
{
//Other fields here
public string PartNumber { get; set; }
[ForeignKey("PartNumber")]
public Part Part { get; set; }
[ForeignKey("Location")]
public string WarehouseID { get; set; }
public Warehouse Location { get; set; }
}
And another
public class Warehouse
{
//Other fields here
public List<Module> Modules { get; set; }
}
Then here is the POCO class where I retrieve the list and for each entity, I come up with a single string thats bound to my datagridview.
public class Part{
//Other fields
public string Locations
{
get
{
//I don't know how efficient this is but I feel that it helps
if (Modules.Count() < 1)
return "";
AirtelDataContext context = new AirtelDataContext();
var modules = context.Modules.Include("Location")
.Where(e => e.PartNumber == PartNumber && e.Location.WarehouseType != "Site")
.Select(a => a.Location.WarehouseName)
.ToList();
var q = from x in modules
group x by x into g
let count = g.Count()
orderby count descending
select (g.Key + " (" + count + ")").ToString();
return String.Join(", ", q);
}
}
}
It is this read-only Location property that I would like to improve its efficiency. My database (MySql) will hold less than 7000(maybe 2000 at most Part entities, 2000 warehouse entities and at most 5000 module entities)
If I can improve performance a little bit I would appreciate. It takes more than 10 seconds to load part entities to the DataGridView.
You could try pushing the query to the server by not calling ToList
on the prior query:
var modules = context.Modules.Include("Location")
.Where(e => e.PartNumber == PartNumber &&
e.Location.WarehouseType != "Site")
.Select(a => a.Location.WarehouseName);
//.ToList();
var q = from x in modules
group x by x into g
let count = g.Count()
orderby count descending
select (g.Key + " (" + count + ")").ToString();
Or just merge the grouping and counting into one query:
var modules = context.Modules.Include("Location")
.Where(e => e.PartNumber == PartNumber &&
e.Location.WarehouseType != "Site")
.GroupBy(a => a.Location.WarehouseName);
.Select(g => g.Key + " (" + g.Count() + ")");
EDIT
Since you are dealing with EF, which cannot directly translate your projection to SQL, your next bet would be to geep the grouping in SQL and do the string concatenation in Linq-to-Objects:
var modules = context.Modules.Include("Location")
.Where(e => e.PartNumber == PartNumber &&
e.Location.WarehouseType != "Site")
.GroupBy(a => a.Location.WarehouseName);
.Select(g => new {g.Key, Count = g.Count()})
.AsEnumerable() // shift to linq-to-objects
.Select(g => g.Key + " (" + g.Count + ")");