Geek Answers Handbook

Convert displayed query / subquery to group?

Update: I have an SQL query, but I still have problems converting it to linq2sql. See code below ..

I have a linq query that retrieves a lot of data, but sometimes it expires due to the load it puts on the sql server. I'm going to work on getting more efficient data (adding indexes, etc.), but I also heard that using a group would be more efficient than using an auxiliary query. Will the group be more effective and if so, what will my request below with the group look like? While I am not familiar with the group. Please do not use lambda

Edit: A new request that is still slow and might be missing:

var query = (from s in db.ZipCodeServiceAvailabilities join a in db.pdx_apart_views on s.ZipCode equals a.Zip_Code.Substring(0, 5) into a_join from a in a_join.DefaultIfEmpty() join b in db.ZipCodeBoundaries on s.ZipCode equals b.ZipCode into b_join from b in b_join.DefaultIfEmpty() where (s.IsServiced == 1 && b.Ordering % 10 == 0 && s.State == "AL") group new { s, b, a } by new { s.ZipCode, s.IsServiced, b.Longitude, b.Latitude, b.Ordering } into g orderby g.Key.ZipCode, g.Key.Ordering select new { g.Key.ZipCode, apartCount = g.Count(p => paApartment_complex != null), Longitude = g.Key.Longitude, Latitude = g.Key.Latitude }).ToArray();

Edit: The query in sql I want in linq2sql (very fast):

 select s.ZipCode, count(distinct ident) ApartCount, b.Longitude, b.Latitude from ZipCodeServiceAvailability s left join pdx_apart_view on s.ZipCode = left([Zip Code], 5) left join ZipCodeBoundaries b on s.ZipCode = b.ZipCode Where IsServiced = 1 and and Ordering % 10 = 0 and State = 'AL' Group By s.ZipCode, IsServiced, b.Longitude, b.Latitude, b.Ordering Order by s.ZipCode, b.Ordering

The original request is very slow:

 var zips = (from s in db.ZipCodeServiceAvailabilities join b in db.ZipCodeBoundaries on s.ZipCode equals b.ZipCode where (s.IsServiced == service && b.Ordering % 10 == 0 && s.State.Contains(state)) orderby b.ZipCode select new { zipCode = b.ZipCode.Trim(), latitude = b.Latitude, longitude = b.Longitude, apartCount = (from a in db.pdx_apart_views where a.Zip_Code.Remove(5) == b.ZipCode select a.Apartment_complex).Count() }).ToArray();
+4
source share
2 answers

It is not possible to duplicate the left [(zip code), 5] sql method in linq2sql without violating the index. The answer was to go with direct ado.net to get all sql functionality.

+1
source

Do you mean that you are not familiar with grouping at all or only in the linq context?

It’s easier for me to write plain SQL than linq when I don’t have entity definitions, and below is what I think you are looking for in SQL. Converting back to linq should be pretty simple.

 select b.ZipCode zipCode, b.Latitude latitude, b.Longitude longitude, count(a.Apartment_complex) apartCount from ZipCodeServiceAvailabilities s join ZipCodeBoundaries b on s.ZipCode = b.ZipCode left join pdx_apart_views a on substring(a.Zip_Code, 1, 5) = b.ZipCode group by ZipCode, Latitude, Longitude
+2
source

More articles:


All Articles