CODEWITHSUNDEEP
c#sql-serverado.netwcf-data-services
DanM
DanM

Reputation: 7197

Linq query combining ADO.Net entities from a database connection and a data service reference?

I'm not even quite sure how to properly word this, but here goes.

I have two .net web applications. One provides a WCF data service ("DEPT_DataService") which provides access to a number of entity sets ("DEPT_Entities"). The other is an MVC2 application which provides a web interface, and also has its own ADO.net entities which are backed by a local MSSQL database.

Using an overly-basic structure to illustrate:

Let's say the WCF data service application includes a single entity set, "Departments", backed by a "Departments" table in its local SQL database. This contains a list of all the departments in the company, along with the employee ID of the primary contact for that department.

Let's also say that the MVC2 application includes a single entity set, "Employees", which contains a list of a bunch of people, including their name and their employee ID.

I've got DEPT_Entities added to the MVC2 application as a Service Reference, "DEPT". When I look at that service reference in the Object Browser, I see "DEPT_Entities" and "Departments".

What I want to do is define a relationship that allows me, via Linq, to refer to something like this:

Employee firstEmployee = db.Employees.First();
Department[] firstEmployeesDepts = firstEmployee.Departments.toList();

...in other words, I essentially want a navigation property that provides a one-to-many relationship between the Employee entities found in the local database and the Department entities found in the remote data service.

Is this doable? How?

Thanks!

Upvotes: 2

Views: 338

Answers (1)

usr
usr

Reputation: 171226

If you want to access the databases directly without a webservice:

You can use SQL Server tricks (Remote Query inside of a View to a Linked Server) to make Linq2SQL be able to query the remote table. Essentially, you simulate that the remote table is just an ordinary local table (a view, but L2S does not care).

This will work but it will not be pretty.

If you want to have a webservice in front of one of the tables: This will not work because L2S does not understand webservices.

In fact, I would architecturally advise against hinding the fact that you are calling a webservice.

Upvotes: 1

Related Questions