C# Linq efficient way to count items on a List on the fly

c# entity-framework entity-framework-6 linq winforms

Question

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.

1
1
7/2/2015 9:55:07 PM

Popular Answer

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 + ")");
3
7/7/2015 1:59:49 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow