Optimize ef-core query

Question

does anyone have any ideas how to improve or optimize this query in terms of performance? An Include cannot be used due to missing Foreign Keys / Navigation Properties because this is a scaffolded model.

using (var session = new Typo3DBContext())
            {
                var countryList = session.TxNeustageodataDomainModelCountry
                            .Where(x => x.Deleted == 0)
                            .Join(session.TxNeustameinereiseDomainModelTripCountryMm,
                                    country => (uint)country.Uid,
                                    tripMM => tripMM.UidForeign,
                                    (country, tripMM) =>
                                        new
                                        {
                                            country = country,
                                            tripMM = tripMM
                                        })
                            .Join(session.TxNeustameinereiseDomainModelTrip,
                                    combinedEntry => combinedEntry.tripMM.UidLocal,
                                    trip => trip.Uid,
                                    (combinedEntry, trip) =>
                                        new
                                        {
                                            combinedEntry = combinedEntry,
                                            trip = trip
                                        })
                            .GroupBy(
                                temp =>
                                    new
                                    {
                                        Name = temp.combinedEntry.country.Name,
                                        Iso = temp.combinedEntry.country.Iso,
                                        Id = temp.combinedEntry.tripMM.UidForeign,
                                        Status = temp.trip.Status,
                                        Deleted = temp.trip.Deleted
                                    },
                                temp => temp.combinedEntry.tripMM
                            )
                            .Where(x => x.Key.Status == 2 && x.Key.Deleted == 0)
                            .Select(
                                group =>
                                    new CountryHelperClass
                                    {
                                        Count = group.Count(),
                                        Iso = group.Key.Iso,
                                        Name = group.Key.Name,
                                        Id = group.Key.Id

                                    })
                            .ToList();

                return countryList;
            }

You may use raw sql for fetching data from db. complex linq queries may lead to performance implication if not handled properly — Tassadaque
– Tassadaque, Commented Jun 24, 2020 at 18:54
@Tassadaque I wouldn't like to use raw sql, is there no suitable extension? — dev.bartels
– dev.bartels, Commented Jun 24, 2020 at 18:57
It's very likely that the best way to improve performance is by setting up indexes by profiling the resulting SQL query. — juharr
– juharr, Commented Jun 24, 2020 at 18:57
If your code works, this question might be a better fit for Code Review. From codereview.stackexchange.com/help/on-topic: If you have a working piece of code from your project and are looking for open-ended feedback in the following areas... Performance... then you are in the right place — dbc
– dbc, Commented Jun 24, 2020 at 20:00
A tiny thing you can do if you are not making any changes to the resultset is to add .AsNoTracking() to your tables. — Lawrence Johnson
– Lawrence Johnson, Commented Jun 25, 2020 at 0:52

Tassadaque · Accepted Answer · 2020-06-24 19:09:15Z

1

You may analyze the generated SQL first and see if optimal sql is being generated. you may follow the this link to start. Another good tool to work with linq queries is to use LINQPad. Some of the common issue with Linq queries are

The ‘N+1 Select’ problem (If you are using ef core 3 This and other sql related issue re being optimized):
To greedy with row and columns
Change Tracking related issues
Missing indexes

Details of these issue can be found in above link an on internet also

Normally i go for stored procedure approach for complex queries as it saves lot of time of optimization of queries

answered Jun 24, 2020 at 19:09

Tassadaque

8,19715 gold badges59 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

dev.bartels Over a year ago

Thanks, it was partly helpful. Are you aware of any Linq extensions?

Tassadaque Over a year ago

i assume you are looking for include alternative you may see stackoverflow.com/questions/48566437/linq-includes-alternative

Tassadaque Over a year ago

By Scaffold you mean you are using db first approach? then you should be relationship in db and you will navigation property

Tassadaque Over a year ago

Then you have to go with join and it gives you better performance as you could join subquery issue if you are using old efcore version

dev.bartels Over a year ago

By Scaffold means entity type classes and a DbContext class based on a database schema. that my definition

Collectives™ on Stack Overflow

Optimize ef-core query

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related