CODEWITHSUNDEEP
c#linqentity-frameworklinq-to-entitieswcf-data-services
Dave Clemmer
Dave Clemmer

Reputation: 3781

How can I join data with Linq to Entities and WCF Data Services?

I have 4 entities that are related as follows:

LocalAgency<-0..1----1->Agency<-0..1----1->Organization<-0..1----1->Customer

In other words, a LocalAgency has one related Agency, etc. The data model is set up using Entity Framework (containing navigation properties to peruse those relationships), and a WCF DataService is set up to provide that data to clients.

On the client side that consumes the DataService, I'm trying to return a query of local agencies based on a customer name, but haven't found a supported way to formulate this simple query.

The first method I tried was using Expand as follows:

var items = (from i in Context.LocalAgencies.Expand("Agency").Expand("Organization").Expand("Customer")
             where (String.IsNullOrEmpty(CustomerName) || i.Agency.Organization.Customer.CustomerName.Contains(CustomerName))
             select i).Skip(StartIndex).Take(PageSize).ToList<LocalAgency>();

This approach works if the "join" is only 1 level deep, but this fails to get navigation properties of navigation properties.

I then tried a join as follows:

var items = (from localAgency in Context.LocalAgencies
             join agency in Context.Agencies on localAgency.CustomerID equals agency.CustomerID
             join organization in Context.Organizations on localAgency.CustomerID equals organization.CustomerID
             join customer in Context.Customers on localAgency.CustomerID equals customer.CustomerID
             where (String.IsNullOrEmpty(CustomerName) || customer.CustomerName.Contains(CustomerName))
             select localAgency).Skip(StartIndex).Take(PageSize).ToList<LocalAgency>();

But, join is not supported in this instance.

I then tried using the Except method as follows:

IQueryable<LocalAgency> items = Context.LocalAgencies;
items = items.Except(from i in items
                     where (String.IsNullOrEmpty(CustomerName) || i.Agency.Organization.Customer.CustomerName.Contains(CustomerName))
                     select i).Skip(StartIndex).Take(PageSize);

But, Except is not supported in this instance.

What am I missing? Do I need to set up something on the DataService side to allow a simple join along defined navigation properties?

Upvotes: 1

Views: 2771

Answers (1)

Dave Clemmer
Dave Clemmer

Reputation: 3781

I used the wrong syntax on the Expand. I did the following:

var items = (from i in Context.LocalAgencies.Expand("Agency").Expand("Agency/Organization").Expand("Agency/Organization/Customer")
where (String.IsNullOrEmpty(CustomerName) || i.Agency.Organization.Customer.CustomerName.Contains(CustomerName))
select i).Skip(StartIndex).Take(PageSize).ToList<LocalAgency>();

Upvotes: 2

Related Questions