CODEWITHSUNDEEP
c#linq
Haroon
Haroon

Reputation: 3472

Linq - optimize/correct my query

I have this linq query (not sure if its correct) but what I want is:

Get me all my company offices (that are related to my company e.g. companyid == mycompanyid) that have declared they have postcode "cv", return back only offices. (code for clarity)

var offices = from office in _readOnlySession.All<Office>()
.GetMyOffices(_userSession) //filter out my offices using extension method
let postcodes = _readOnlySession.All<OfficePostCode>().Where(x => x.OfficeID == office.OfficeID)
                .Join(_readOnlySession.All<PostCodeDistrict>().Where(r=> r.Region.ToLower().StartsWith("cv".ToLower())),
                          x => x.PostCodeID,
                          y => y.PostCodeID,
                          (x, y) => new { Region = y.Region })
where postcodes.Any()
select new { office.OfficeID, office.Name };

Question: how can I make this all a query method, a more optimized/correct query method?

Note: "cv" is going to be a variable passed into the method - kinda hardcoded to illustrate my example

Update:

    IQueryable<T> All<T>() where T : class, new();

    public IQueryable<T> All<T>() where T : class, new()
    {
        return GetTable<T>().AsQueryable();
    }

Upvotes: 0

Views: 126

Answers (3)

Bodrick
Bodrick

Reputation: 342

I've assumed that OfficePostCode and Office both have PostCodeID properties, you might need to change the last .Where() clause to fit the properties you have. This should do what you want though, and IMO is a bit easier to read. 

public IEnumerable<Office> GetOffices (string postCode)
{
    List<Office> myOffices = _readOnlySession.All<Office> ()
        .GetMyOffices (_userSession)
        .ToList (); // Get all the offices you are interested in.


    List<OfficePostCode> postCodeDistricts = _readOnlySession
        .All<OfficePostCode> ()
        .Where (x => x.Region.StartsWith (postCode, true, System.Globalization.CultureInfo.InvariantCulture))
        .ToList (); // A list of OfficePostCodes with the specified region.

    // Using the 3 parameter overload for StartsWith lets you specify a case invariant comparison,
    // which saves you from having to do .ToLower().

    return myOffices.Where (o => postCodeDistricts.Any (pcd => o.PostCodeID == pcd.PostCodeID));
}

Of course, you can compact this down by removing the intermediate variables, but I personally find it clearer this way. It makes debugging easier as well, as you can put breakpoints on the intermediate variables.

Upvotes: 1

Hans Jonus
Hans Jonus

Reputation: 385

Something like this maybe?

var offices = _readOnlySession.All<Office>()
    .GetMyOffices(_userSession) //filter out my offices using extension method
    .Where(office => office.PostCodes.Any(pc => pc.District.Region.ToUpperInvariant().StartsWith("CV")));

Upvotes: 0

Petr Behensk&#253;
Petr Behensk&#253;

Reputation: 620

It seems to be OK. Only what can but also cannot work better is to execute let postcodes part without OfficeID condition before main query and then use it in main query like:

where postcodes.Any(pc => pc.OfficeID == office.OfficeID)

Upvotes: 0

Related Questions