LINQ database query

Question

I make a query to the database, first I take the data from the Technologies table, then there is a search, sorting + paginating. It turns out a lot of similar code.

 switch (sort) { case "Low": items.Tvsets = db.Technologies.Where(x => x.Name.Contains(tech)) .Include(p => p.Tvsets.Select(x => x.Company)) .FirstOrDefault() ? .Tvsets .Where(x => x.Name.Contains(search) || x.Company.Name.Contains(search) || x.Technology.Name.Contains(search)) .OrderBy(x => x.Price) .Skip((pageInfo.PageNumber - 1) * pageInfo.PageSize) .Take(GetAll(pageInfo.TotalItems, pageInfo.PageSize, pageInfo.PageNumber)) .ToList(); return View(items); default: ViewBag.Sort = "High"; items.Tvsets = db.Technologies.Where(x => x.Name.Contains(tech)) .Include(p => p.Tvsets.Select(x => x.Company)) .FirstOrDefault() ? .Tvsets .Where(x => x.Name.Contains(search) || x.Company.Name.Contains(search) || x.Technology.Name.Contains(search)) .OrderByDescending(x => x.Price) .Skip((pageInfo.PageNumber - 1) * pageInfo.PageSize) .Take(GetAll(pageInfo.TotalItems, pageInfo.PageSize, pageInfo.PageNumber)) .ToList(); return View(items); }

How can I reduce the code? Maybe there are any patterns or other ways?

Please note that you are dragging the entire Tvsets collection from the DBMS onto the client without filtering on the base side.
Considering that there are enough records to break them into pages - this is a gross mistake.

Nikita 1,389 6 silver marks 21 bronze marks · Accepted Answer · 2017-12-20T23:23:54

try this:

 var tvSets = db.Technologies .Where(x => x.Name.Contains(tech)) .Include(p => p.Tvsets.Select(x => x.Company)) .FirstOrDefault() ? .Tvsets .Where(x => x.Name.Contains(search) || x.Company.Name.Contains(search) || x.Technology.Name.Contains(search)).AsQueryable(); if (sort == "Low") { tvSets = tvSets.OrderBy(x => x.Price); } else { ViewBag.Sort = "High"; tvSets = tvSets.OrderByDescending(x => x.Price); } items.Tvsets = tvSets.Skip((pageInfo.PageNumber - 1) * pageInfo.PageSize) .Take(GetAll(pageInfo.TotalItems, pageInfo.PageSize, pageInfo.PageNumber)) .ToList(); return View(items);

Those. We distinguish the common in both requests, and the difference depending on the parameter. In this case, you have only one difference in sorting. Here it is allocated. The request will not be executed until .ToList () is called. Until then, it can be collected in pieces, without fear that the server will leave a piece of the request.

The explanation about the "request will not be executed until you have done ToList " is incorrect.

LINQ database query

1 answer 1

More articles: