Advanced Queries

Supported Query Builder Methods

Distinct
Except
GroupBy
Intersect
OfType
OrderBy
OrderByDescending
Select
SelectMany
Skip
SkipWhile
Take
TakeWhile

Sample Query

This example returns the employees that have been contacted by the head office of an organization. The contacts are made through documents and conversations. First the Employee and the office Id's from the list of sent conversations are fetched. Then the same is done for documents. A union of the two list of office is done, and then a distinct to get the distinct office Id and Employee Id. The same employee can be related to multiple offices, hence the distinct on both office and Employee Id. Then a join is done on the Employee Id and Location Id between the obtained list and the Employee's table to get the actual EmployeeOffice Entity Collection.

    public IQueryable<employee> GetContactedEmployees(long headOfficeId)
    {
        var convEmployees = EntityContext.CreateQuery<Conversation>()
            .Where(c => c.HeadOfficeId == headOfficeId)
            .Select(c => new { EmployeeId = c.EmployeeId, LocationId = c.EmployeeLocationId });

        var documentEmployees = EntityContext.CreateQuery<Document>()
            .Where(d => d.HeadOfficeId == headOfficeId)
            .Select(c => new { EmployeeId = c.EmployeeId, LocationId = c.EmployeeLocationId });

        var employees = EntityContext.CreateQuery<EmployeeOffice>()
            .Join(convEmployees.Union(documentEmployees).Distinct(),
                p => new { p.EmployeeId, p.LocationId },
                pl => new { pl.EmployeeId, pl.LocationId },
                (p, pl) => p);
        return employees;
    }