How to make a better LINQ query for this

Question

I was doing my project in MVC3 with Entity Framework. I dont have much experience with LINQ. But I managed to code based on my requirement. Now, The thing is that, My logic takes more time to process the query because of i split up my query using foreach and there are three different type in single column on the DB. I was trying to code the complete logic using LINQ itself. But didn't help me anything.

I need a suggestion that how can i change my logic to improve my code performance. Pls help me for this.

Action Method Code

        public decimal ReturnAmount(int Id, int Year)
    {

        var UsersWithDeptId = db.Users.Where(asd => asd.UserDeptId == Id).Select(asd => asd.Id).ToList();
        var ListUserValue = (from cap in db.CareAllocationPercents
                             where cap.Type == 1 && UsersWithDeptId.Contains(cap.UserId)
                             select new UserWithDeptId
                             {
                                 Year = (from amt in db.CareAllocations where amt.CareItemId == cap.CareItemId select amt.Year).FirstOrDefault(),
                                 Amount = (from amt in db.CareAllocations where amt.CareItemId == cap.CareItemId select amt.Amount).FirstOrDefault(),
                                 UserId = cap.UserId,
                                 UserDeptId = (from userdept in db.Users where userdept.Id == cap.UserId select userdept.UserDeptId).FirstOrDefault(),
                                 Percentage = cap.Percentage,
                                 CareItemId = cap.CareItemId,
                                 Category = "User",
                                 CareAllocationId = cap.Id
                             }).ToList();

        ListUserValue = ListUserValue.Where(asd => asd.Year == Year).ToList();

        List<int> RouteIds = db.CareAllocationPercents.Where(asd => asd.Type == 3).Select(asd => asd.UserId).ToList();
        var UsersWithRoutingId = (from route in db.RoutingListMembers
                                  where RouteIds.Contains(route.RoutingListId.Value) && route.User.UserDeptId == Id
                                  select
                                      new RoutingWithUser
                                      {
                                          UserId = route.UserId,
                                          RoutingId = route.RoutingListId
                                      }).ToList();

        var ListRouteValue = (from cap in db.CareAllocationPercents
                              where cap.Type == 3
                              select new UserWithDeptId
                              {
                                  Year = (from amt in db.CareAllocations where amt.CareItemId == cap.CareItemId select amt.Year).FirstOrDefault(),
                                  Amount = (from amt in db.CareAllocations where amt.CareItemId == cap.CareItemId select amt.Amount).FirstOrDefault(),
                                  UserId = cap.UserId,
                                  UserDeptId = (from userdept in db.Users where userdept.Id == cap.UserId select userdept.UserDeptId).FirstOrDefault(),
                                  Percentage = cap.Percentage,
                                  Category = "Route",
                                  CareItemId = cap.CareItemId,
                                  CareAllocationId = cap.Id
                              }).ToList();

        List<UserWithDeptId> NewRouteList = new List<UserWithDeptId>();
        ListRouteValue = ListRouteValue.Where(asd => asd.Year == Year).ToList();

        foreach (var listdept in ListRouteValue)
        {
            var user = UsersWithRoutingId.Where(uwri => uwri.RoutingId == listdept.UserId).FirstOrDefault();
            if (user != null)
            {
                NewRouteList.Add(new UserWithDeptId { UserId = user.UserId, Year = listdept.Year, UserDeptId = db.Users.Where(asd => asd.Id == user.UserId).Select(asd => asd.UserDeptId).FirstOrDefault(), Percentage = listdept.Percentage, CareItemId = listdept.CareItemId, Amount = listdept.Amount, CareAllocationId = listdept.CareAllocationId, Category = listdept.Category });
            }
        }

        NewRouteList = NewRouteList.Where(asd => asd.UserDeptId == Id).ToList();
        var ListUserId = (from user in db.Users
                          where user.UserDeptId == Id
                          select new UserWithDeptId
                          {
                              UserId = user.Id
                          }).ToList();

        var ListDeptId = (from cap in db.CareAllocationPercents
                          where cap.Type == 2 && cap.UserId == Id
                          select new UserWithDeptId
                          {
                              Year = (from amt in db.CareAllocations where amt.CareItemId == cap.CareItemId select amt.Year).FirstOrDefault(),
                              Amount = (from amt in db.CareAllocations where amt.CareItemId == cap.CareItemId select amt.Amount).FirstOrDefault(),
                              UserDeptId = cap.UserId,
                              Percentage = cap.Percentage,
                              Category = "Dept",
                              CareItemId = cap.CareItemId,
                              CareAllocationId = cap.Id,
                          }).ToList();

        ListDeptId = ListDeptId.Where(asd => asd.Year == Year).ToList();
        int UserCount = ListUserId.Count;
        List<UserWithDeptId> NewList = new List<UserWithDeptId>();

        foreach (var listdept in ListDeptId)
        {
            foreach (var users in ListUserId)
            {
                NewList.Add(new UserWithDeptId { UserId = users.UserId, UserDeptId = listdept.UserDeptId, Percentage = listdept.Percentage, CareItemId = listdept.CareItemId, Amount = listdept.Amount, CareAllocationId = listdept.CareAllocationId, Category = listdept.Category });
            }
        }
        int CountUser = ListUserValue.Count;
        int RouteCount = NewRouteList.Count;
        NewList.AddRange(ListUserValue);
        NewList.AddRange(NewRouteList);
        List<CAREReviewBefore> NewModelList = new List<CAREReviewBefore>();
        foreach (var mod in NewList)
        {
            CAREReviewBefore cr = new CAREReviewBefore();
           // int? BibId = (from pod in db.PoDetails where pod.Id == (from por in db.CareItems where por.Id == mod.CareItemId select por.PoReceipt.PoDetailId).FirstOrDefault() select pod.BibId).FirstOrDefault();
            int? InvoiceId = (from asd in db.PoReceipts
                              where asd.Id == (from careits in db.CareItems where careits.Id == mod.CareItemId select careits.PoRecId).FirstOrDefault()
                              select asd.InvoiceId).FirstOrDefault();

            //Current Currency Rate

            var Rate = db.Invoices.Where(In => In.Id == InvoiceId).Select(In => In.Rate).FirstOrDefault();
            var CurrencyRate = db.Invoices.Where(inv => inv.Id == InvoiceId).Select(inv => inv.Currency.Rate).FirstOrDefault();
            decimal Rat = 0;
            if (Rate != null || Rate != 0)
            {
                Rat = Convert.ToDecimal(Rate);
                if (Rat == 0)
                {
                    Rat = Convert.ToDecimal(CurrencyRate);
                }
                cr.Tot_Annual_SubCost = mod.Amount * Rat;
            }

            if (mod.Category == "User")
            {
                cr.Allocated_Cost_to_Dept = (((mod.Amount * mod.Percentage) / (100)) * Rat);
            }
            else if (mod.Category == "Dept")
            {
                cr.Allocated_Cost_to_Dept = (((mod.Amount * mod.Percentage) / (UserCount * 100)) * Rat);
            }
            else if (mod.Category == "Route")
            {
                cr.Allocated_Cost_to_Dept = (((mod.Amount * mod.Percentage) / (RouteCount * 100)) * Rat);
            }
            NewModelList.Add(cr);

        }
        var Amount = NewModelList.Sum(asd => asd.Allocated_Cost_to_Dept);
        return Amount;
    }

Thanks,

Answer 1

One improvement is to get the list in one take after you fetched the data you need for the filters:

from cap in db.CareAllocationPercents
where (cap.Type == 1 && UsersWithDeptId.Contains(cap.UserId))
   || (cap.Type == 2 && cap.UserId == Id)
   || cap.Type == 3
select new UserWithDeptId
{
    Year = (from amt in db.CareAllocations where amt.CareItemId == cap.CareItemId select amt.Year).FirstOrDefault(),
    Amount = (from amt in db.CareAllocations where amt.CareItemId == cap.CareItemId select amt.Amount).FirstOrDefault(),
    UserId = cap.UserId,
    UserDeptId = (from userdept in db.Users where userdept.Id == cap.UserId select userdept.UserDeptId).FirstOrDefault(),
    Percentage = cap.Percentage,
    CareItemId = cap.CareItemId,
    Category = "User",
    CareAllocationId = cap.Id,
    Type = cap.Type
})

In the last part you use Type in stead of mod.Category to calculate the right Allocated_Cost_to_Dept amounts.

Answer 2

I'd recommend you to use entity framework profiler, I've used their nhibernate profiler and it was really good, sadly its' not free, but the trial version gives you enough time.

http://www.hibernatingrhinos.com/products/EFProf

Answer 3

The only thing that sticks out to me at the moment is the .ToList() calls you're making. .ToList() will materialize the IQueryable, which executes the query against the database. i'd suggest doing that as little as possible so that the majority of the query can be run against the database instead of pulling back all of the table rows and then filtering it in code.

The other suggestion I'd have is to set up a trace in Sql Server Profiler and watching the queries that you are actually executing against the database.