CODEWITHSUNDEEP
c#linq
Aleksandr Fursenko
Aleksandr Fursenko

Reputation: 37

LINQ to SQL how optimise query?

There are:

enter image description here

Input parametr CompanyName.

api/search/companies?companyName=Bo&page=1&limit=20

Task: get Companies by CompanyName and first Industry in each Company:

enter image description here

Query: 

 var query = (from company in _context.Companies
                         where company.Name.Contains(model.Model.CompanyName.Trim())
                         join ci in _context.CompanyIndustries on company.Id equals ci.CompanyId
                         join industry in _context.Industries on ci.IndustryId equals industry.Id

                         select new SearchCompanyModel()
                         {
                             Id = company.Id,
                             Name = company.Name,
                             Logo = company.LogoUrl,
                             Industry = industry.Name
                         })
                         .GroupBy(x => new {x.Id, x.Name, x.Logo})
                         .Select(x => new SearchCompanyModel
                {
                    Id = x.Key.Id,
                    Name = x.Key.Name,
                    Logo = x.Key.Logo,
                    Industry = x.FirstOrDefault().Industry
                });

Upvotes: 0

Views: 129

Answers (2)

mark_h
mark_h

Reputation: 5487

There may be several optimizations you could try but if you use properties of a of a non-mapped object type (i.e. model.Model.CompanyName.Trim()) this may prevent the query from being cached (see section 4.3)

Fortunately this is easy enough to fix, just cache the value in a local variable outside of the query and use this value instead

var companyName = model.Model.CompanyName.Trim();

This may help a little, but the article to which I have provided a link also contains many other suggestions you could try.

Upvotes: 1

Sergey Kotyushkin
Sergey Kotyushkin

Reputation: 111

Uou can use Join

void Main()
{
    var allCompanies = new Company[] {
        new Company{Id = "1", Name = "c1"}, 
        new Company{Id = "2", Name = "c2"}, 
        new Company{Id = "3", Name = "c2"}
    };

    var allIndustries = new Industry[] {
        new Industry{Id = "1", Name = "i1"}, 
        new Industry{Id = "2", Name = "i2"}, 
        new Industry{Id = "3", Name = "i3"}
    };

    var allCompanyIndustries = new CompanyIndustry[] {
        new CompanyIndustry{CompanyId = "1", IndustryId = "1"}, 
        new CompanyIndustry{CompanyId = "1", IndustryId = "2"}, 
        new CompanyIndustry{CompanyId = "3", IndustryId = "1"}
    };

    var companyName = "c1";
    var companies = allCompanies
        .Where(company => company.Name == companyName)
        .Join(allCompanyIndustries, c => c.Id, ci => ci.CompanyId, (c, i) => new {Company = c, IndustryId = i.IndustryId})
        .Join(allIndustries, ci => ci.IndustryId, i => i.Id, (ci, i) => new {Company = ci.Company, Industry = i})
        .ToArray();
}

class Company {
    public string Id {get;set;}
    public string Name {get;set;}
}

class Industry {
    public string Id {get;set;}
    public string Name {get;set;}
}

class CompanyIndustry {
    public string CompanyId {get;set;}
    public string IndustryId {get;set;}
}

Upvotes: 1

Related Questions