What is the right way to do a multi-table join query in ASP.NET to SQL Server via LINQ?

Question

I'm working on an ASP.NET 4.0 site, which I inherited ownership of.

It has a number of existing LINQ datasources pointing to individual tables. For example, to the Patient Encounter Summary table in SQL Server.

The problem is in displaying the data there. It's a normalized database, so that table contains (for example) the provider ID, rather than the provider name.

It's simple enough to join the Patient Counter Summary to the Providers table (in SQL)... but how does one do that in ASP.NET? I'm not sure of the correct nomenclature, but the 'mid layer' is VB.

So, the existing LinqDataSource entries are all to individual tables. I tried building a view in SQL Server to do all the joins, but the LinqDataSource doesn't 'see' such views as an option... even if it's read-only (no update, delete, or insert).

Please pardon my ignorance, but it looks like it should be so simple. For example, I have things like <asp:Label ID="Label12" runat="server" Text='<%# Bind("AttendingPhysicianID") %>'></asp:Label>... that it appears to me would be so simple if the LINQ DataSource was a view that joined the tables so I could change it to PhysicianName coming from the Physicians table.

Any guidance would be most appreciated... ideally at the level of 'this guy doesn't know anything'. :-) That is, I've seen all sorts of what looks like VB code on answers to similar questions, but don't know how to implement that within this structure.

Thanks much.

Are you using Entity Framework at all? – Scottie Jan 29 '12 at 23:31 — Scottie, Jan 29 '12 at 23:31

score 0 · Answer 1 · answered Jan 29 '12 at 23:30

0

I would recomment to bookmark 101 LINQ samples, it is quite helpful.

Here is answer to your question: http://msdn.microsoft.com/en-us/vstudio/bb737909

answered Jan 29 '12 at 23:30

st78

8,028
11
49
68

score 0 · Answer 2 · answered Jan 29 '12 at 23:37

0

Although this question is to do with LinqToSql joins, you state you are attempting to do this because you couldn't expose a pre-written SQL View via the Linq DataSource.

If there is value in using the SQL View you've written, and in having that view accessible outside your VB libaries, I would suggest going the first route and getting the LinqDataSource to expose your SQL View.

Try looking here: http://msdn.microsoft.com/en-us/library/bb384396.aspx

answered Jan 29 '12 at 23:37

Stafford Williams

9,696
8
50
101

Thank you, Stafford. I like the sound of this solution - I would indeed like to keep and use the SQL views in my database. I think what's got me stymied are limitations of VS2010 Express. Namely, on that site, it talks about choosing Show Data Sources on the Data menu. I don't have a Data menu; the closest I can get is to show the Data Design toolbar, which looks to have some of the desired options, but all of them are grayed out. Is there a way to accomplish this specifically within the limitations of VS Express? – ThunderSi Jan 31 '12 at 23:09
It's been ages since I've used vs express, however i remember it being pretty restrictive when it came to SQL Server development. You could check out http://stackoverflow.com/questions/188963/connecting-to-sql-server-with-visual-studio-express-editions. – Stafford Williams Feb 01 '12 at 01:50

score 0 · Answer 3 · edited Jun 20 '20 at 09:12

You're using a System.Web.UI.WebControls.LinqDataSource. Imo, this is a control that lets you use strings to opaquely interact with (what was) a compiler verified query generation technology.

Here's a link to the documentation. http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.linqdatasource.aspx

Here's some relevant passages:

Select - Gets or sets the properties and calculated values that are included in the retrieved data.

The LinqDataSource control enables you to use LINQ in an ASP.NET Web page by setting properties in markup text. The LinqDataSource control uses LINQ to SQL to automatically generate the data commands.

When you are querying a database... You then set the ContextTypeName property to the class that represents the database and set the TableName property to the property that represents the database table.

5.Select (specify which fields or properties to return).

However, if you set the Select property, it means that automatic update, insert, and delete operations cannot be enabled.

So I would think you could include a subquery in the select property to convert an Order.CustomerId into a Customer.Name

But if I were in your shoes, I would abandon the LinqDataSource and just write LinqToSql compiler verified queries.

What is the right way to do a multi-table join query in ASP.NET to SQL Server via LINQ?

3 Answers3