Entity LINQ query to slow

Question

I'm new to the C#, I have a database that someone else designed, query works great, but compared with SQL, it's 10 times slower.

I made mistakes here for sure, anybody have tips to speed this up a little bit. This model is for displaying in table, and I converting int to ENUM and calculating discount for display.

Code is:

var results = from w in db.Washes.AsEnumerable()
              join t in db.Wash_Types.AsEnumerable() on w.WashTypeId equals t.Id
              join a in db.Accounts.AsEnumerable() on w.AccountId equals a.Id
              orderby w.Id descending
              select new AllWashesTable
                    {
                        Id = w.Id,
                        WashTime = w.WashTime,
                        WashTimeEnd = w.WashTimeEnd,
                        Name = a.Name,
                        Client = (w.Client != null ? w.Client.Naziv : ""),
                        MobileNumber = a.MobileNumber,
                        Identification = w.Identification,
                        WashType = WashTypeShowEnum.WashTypeShowEnumToString((WashTypeShowEnum.WashType) w.WashTypeId),
                        Price = int.Parse(t.WashPrice) * (1 - w.Discount) + "",
                        Discount = w.Discount
                    };
return results.ToList();

Seems all my entity queries are at least 5+ times slower than SQL. Somewhere I am making some mistake.

@AleksAndreev can't call method for converting type(int) to Enum(string) without `AsEnumerable()?` — Boris Dabetic, Feb 24 '18 at 20:51
Problem is, you loose all server side filtering and such. You have to do conversions in read-only properties in `AllWashesTable `for example and remove the `AsEnumerable()`. — Peter Bons, Feb 24 '18 at 20:55

score 2 · Accepted Answer · answered Feb 24 '18 at 21:00

Your problem is the use of AsEnumerable. When the query gets executed (in your case the results.ToList()), Everything that appears after it, will be evaluated using linq-to-objects. It means that your joins will not be handled by DB. You will fetch all the records from the tables.

However, your function WashTypeShowEnum.WashTypeShowEnumToString will not be recognized by entity framework.

You might want to move the AsEnumerable to the end and then select the results.

var results = (from w in db.Washes
              join t in db.Wash_Types on w.WashTypeId equals t.Id
              join a in db.Accounts on w.AccountId equals a.Id
              orderby w.Id descending
              select new {w, a, t}).AsEnumerable().Select(arg=> new AllWashesTable
                    {
                        Id = arg.w.Id,
                        WashTime = arg.w.WashTime,
                        WashTimeEnd = arg.w.WashTimeEnd,
                        Name = arg.a.Name,
                        Client = (arg.w.Client != null ? arg.w.Client.Naziv : ""),
                        MobileNumber = arg.a.MobileNumber,
                        Identification = arg.w.Identification,
                        WashType = WashTypeShowEnum.WashTypeShowEnumToString((WashTypeShowEnum.WashType) arg.w.WashTypeId),
                        Price = int.Parse(arg.t.WashPrice) * (1 - arg.w.Discount) + "",
                        Discount = arg.w.Discount
                    };
return results.ToList();

I measured time for query and it's not to much slow. Problem was DevExpress table that is slow in populating Rows. I didn't managed to resolve that issue. But you helped me with this more logical approach, I learned something and made more faster Entity Query. Accepted. :) — Boris Dabetic, Feb 28 '18 at 14:03
Ofir, any hint to make this Async method. Enumerable can not be async. Any help here? :) — Boris Dabetic, Mar 01 '18 at 10:03
Take a look [here](https://stackoverflow.com/questions/35011656/async-await-in-linq-select) — Ofir Winegarten, Mar 01 '18 at 10:17

Entity LINQ query to slow

1 Answers1