ASP.NET MVC And LINQ General Questions

Question

I've asked a few questions on this topic before. Before we're able to implement either MVC or LINQ at work we need to resolve a few issues.

Multiple Record Sets in ASP.NET MVC

The only examples of MVC in use only have a single result set returned. When using stored procedures multiple record sets can be retrieved, and the whole reason we tend to use stored procedures is for two reasons (which I'm sure many of you are also aware of). Firstly in case we need to pass parameters, and secondly if we want multiple tables of data returned. How is this possible in ASP.NET's MVC architecture?

In this tutorial we see how the data is retrieved. But it uses ViewData.Model which indicates a single result set, it doesn't explain what happens if multiple result sets are returned, or how to get them.

Strongly Typed Stored Procedure Output

Additionally, the examples on the ASP.NET website for using LINQ for strongly typed resolution of output are achieved by using the *.dbml format which is a mirror image of the table schema allowing for the lookup against fields using LINQ. Great. But what happens if your output is custom from a stored procedure which does not map directly to either a view or a table? How do we resolve column names from these stored procedures?

In the previous section I described this tutorial, but this also only shows how to create LINQ to SQL for tables only, not the custom output from a sproc.

LINQ Column Lookups

At work we run a macro which exports a bunch of classes to our App_Code folder so stored procedure parameters are pre-defined. This is done so we don't have to call DeriveParameters which consists of an extra call to the database. We don't want this to happen because there's a lot of traffic as it is. If we're using LINQ, how are column data types resolved? Is there a call to the database everytime we define a parameter to find out the data type and name of the parameter? Have things changed since? Does it still call DeriveParameters every time? Are these cached somewhere?

DBML Formats

Should *.dbml files incorporate all tables from a database? We have about 15 databases with many, many tables in each one.

A View For Every Output

Yet another point to add to this post. Instead of manually creating the dbml classes is it better to represent the data as a view, even if it's custom output? Or is it better to create a custom class in the dbml file?

This Must Be The Last Problem Or I Will Eat My Own Arm

"Unable to cast object of type 'SingleResult`1[IntranetMVC.UserDetail]' to type 'IntranetMVC.UserDetail'."

Here's the function:

  Function Index() As ActionResult
    ViewData("Message") = "Welcome to ASP.NET MVC!"

    Dim userDetail As UserDetail
    Dim office As IList(Of Office)
    Dim activeUser As IList(Of ActiveUser)
    Dim dept As IList(Of Department)

    Using db As PersonnelDataContext = New PersonnelDataContext
      Dim results As IMultipleResults = db.UserDetail(1168)

      userDetail = results.GetResult(Of UserDetail)()
      office = results.GetResult(Of Office)()
      activeUser = results.GetResult(Of ActiveUser)()
      dept = results.GetResult(Of Department)()
    End Using

    Return View(New IndexViewData(userDetail, office, activeUser, dept))
  End Function

It's occurring on all of the userDetail, office, activeUser and dept assignments, but I have no idea why. Now, I haven't mapped them properly yet, but take for example the Department one. I've dragged and dropped the table schema onto the dbml file, so it definitely exists and is in the right format.

UPDATE

Here's my actual code. It's not final, I've been playing around with it. It seems the return types aren't right, but I'm not sure why. It seems to think only a single result is ever returned when the stored procedure actually returns four sets of data. One of those sets only ever has a single result, the others always have multiple rows returned:

Unable to cast object of type 'SingleResult1[IntranetMVC.Office]' to type 'System.Collections.Generic.IList1

Imports System.Data.Linq
Imports System.Reflection
Imports System.Data.Linq.Mapping

Partial Class PersonnelDataContext

  <FunctionAttribute(Name:="dbo.UserDetailProc"), _
  ResultType(GetType(UserDetail)), _
  ResultType(GetType(IList(Of Office))), _
  ResultType(GetType(IList(Of ActiveUser))), _
  ResultType(GetType(IList(Of Department)))> _
  Public Function UserDetail( _
                  <Parameter(Name:="User_Key", DbType:="Int")> ByVal User_Key As Integer, _
                  <Parameter(Name:="EditYN", DbType:="Char")> Optional ByVal EditYN As Char = "N") As IMultipleResults

    Dim result As IExecuteResult = Me.ExecuteMethodCall(Me, CType(MethodInfo.GetCurrentMethod(), MethodInfo), User_Key, EditYN)
    Return CType(result.ReturnValue, IMultipleResults)
  End Function
End Class

FIX

Okay, I didn't realise because to be honest with you I wasn't checking the return types correctly. I assumed that results.GetResult(Of MyType) (from IMultipleResults) would return a collection. On the contrary, it only returns single results and moves the pointer to the next item in the collection. Unfortunately GetResult is the only exposed method for bringing back results, so you have to iterate over the collection and add them to a generic list.

Thanks very much!

Mardis

Answer 1

Multiple Record Sets in ASP.NET MVC

Yep - most definately.

First u need to manually create a method that calls the stored proc, returning an IMultipleResults result.

This blog posts has all the info u need. It's simple to do and very easy and works.

What you need to do is two steps.

1. Create a method that calls the stored procedure and returns multiple records (refer to blog post above).
2. Create a simple class object which is used in the view, and the controller sets the properties.

eg.

IndexViewData.cs
public class IndexViewData
{
    IList<Customers> Customers { get; set; }
    IList<Products> Products { get; set; }
}

.

HomeController.cs
public ActionResult Index()
{
    IList<Customers> customers;
    IList<Products> products;

    // This grabs the multiple records from a single stored procedure. 
    // This code taken from the blog post link, above.
    using (NorthwindDataContext db = new NorthwindDatacontext)
    {
        IMultipleResults results = db.GetMultipleRecordSets(arg1, ....);
        customers = results.GetResult<Customer>();
        products = results.GetProducts<Product>();
    }

    // Now return the view, with the viewdata that is required.
    return View(new IndexViewData
                    {
                        Customers = customers,
                        Products = products
                    });
}

.

Index.aspx
<%@ Page 
    Language="C#" 
    MasterPageFile="~/Views/Shared/Site.Master" 
    Inherits="System.Web.Mvc.ViewPage<IndexViewData>" %>

<% Html.RenderPartial("CustomersUserControl", 
                       ViewData.Model.Customers); %>

 <br/>

<h2>Products</h2>
<% foreach(var product in ViewData.Model.Products) { %>
Name: <%= product.Name %><br/>
<% } %>

...

Please note, i have not done any error checking, etc. it's a really quick pseduo code guide to getting u started.

Note #2: Please take note that the Index view is strongly typed (it inherits the ViewPage.

Strongly Typed Stored Procedure Output

I've answered this, above. Please note, u can strongly type your ISingleResult stored procedures.

LINQ Column Lookups

Ok, i think i understand what you mean, here. When you create your method, which calls the stored procedure (either an ISingleResult or an IMultipleResult) you are defining the paramters that are required, there and then ... think of it as being hard-coded.

When you drag-n-drop tables onto the linq to sql context gui canvas, Visual Studio does a lookup check there and then. It then creates the classes in one of the various files for the context. eg. NorthwindDataContext.designer, etc. So, it's a one-hit-job. Once the class is created, the designer then displays this on the canvas. There is NO SYNC back to the database. None. Nada. Zilch. If you change anything in your database schema (eg. add a new field, change a stored procedure argument, etc) the datacontext will NOT know about it. You need to delete the table and drag-n-drop it back.

Bonus Trick!

If you have SQL Profiler running while you drag-n-drop a table or stored procedure onto the canvas, you can see Visual Studio 'querying' the database for the information. :)

So yeah. It's fire-n-forget. A one-hit-job. Required manual sync.

HTH.

Update

I noticed u added two more q's, so i'll add my answers, under here.

DBML Formats

This is a personal decision. 15 DB's! shees! that's a fair number. Anyways, it comes down to how maintainable your Context canvas becomes. Secondly, every context creates it's OWN database connection. So if your method decides to call 4 contexts, then you've got 4 connections (and round trips) to the db, dude :)

A View For Every Output

Personally, I have all my tables on the Context Canvas. I never use those table classes in my code. They are private and only used in my Repository namespace/project/dll. I THEN use POCO classes to move all my stuff around. This keeps my code, cleaner and not dependant on the repository.

Update #2

This Must Be The Last Problem Or I Will Eat My Own Arm

If you've dragged the stored proc onto the linq context canvas, please delete it. There needs to be no references to the method UserDetails(int userId).

Now, add the following code (you'll need to convert it to VB.NET) to the data context's partial class (i'm assuming u know what that is/means, btw) :-

[Function("UserDetails")] // <-- This is the name of your stored procedure.
[ResultType(TypeOf(UserDetail))]
[ResultType(TypeOf(Office))]
[ResultType(TypeOf(ActiveUser))]
[ResultType(TypeOf(Department))]
public IMultipleResults UserDetails(
    [Parameter(Name = "UserId", DbType = "Int")] int userId)
//                      /\____     /\_____         ____/\                    
// This is where u _define_ the stored proc arguments.
{
    IExecuteResult result = this.ExecuteMethodCall(this, 
           ((MethodInfo)MethodInfo.GetCurrentMethod())), userId);
// This is where all the stored proc arguments are set ____/\
// This can be multiple args. eg. userId, name, ...
    return (IMultipleResults)result.ReturnValue;
}

then use it like u did in your previous VB.NET code.

The problem (i'm guessing) was that you haven't made the method to handle IMultipleResults. You're still using the old stored proc code-signature, which was made (by default) to only be a single recordset result (ie. ISingleResult).

This is the default if u drag-n-drop a stored from from the Server Explorer onto the linq Context Canvas.

Kronfeld

You sir, are a life saver. Let me just try the multiple record sets task to see what I can come up with. Although the multiple record sets returned don't reflect table schemas as it's typically custom output so I may have trouble with this. As for the final point I made, when your code calls sprocs or tables it usually has to find out the columns, and what their data types are. This requires an extra call to the DB which is undesirable. – Mardis

Ah dude - gotcha i think. I've update the last paragraph. Please re-read that and tell me if i got it right (that is, my understanding of your question, part 3). – Kronfeld

Awesome, that answers it perfectly. I'm just having a bash at the multiple result sets. – Mardis

Go nuts! I'm using it all the time. Love Linq 2 Sql :) – Kronfeld

Added more answers, as you added 2 more q's. – Kronfeld

Aye, there's 15 DB's because a) we need the separation as it's extremely important data and b) there IS that much data to the point we need to separate it out. There's about 100 stored procedures in each DB, and at least that many tables too. Pretty big schema. Is the context canvas what you look at when you're viewing a dbml file? (sorry for the stupid question, I've never touched this stuff before yesterday) – Mardis

Where does the IndexViewData class live? In the Models folder? Or is it defined in the DataContext partial class? – Mardis

1) Conext canvas what you look at when you're viewing the dbml file. Correct 2) I put the XXXX_ViewData classes side-by-side to the views they represent. So if this is the Index view, for the Home controller, then i will put the IndexViewData in the View\Home folder. Other peeps put it in the Models folder and give it a uniquish name. – Kronfeld

Okay, and also I'm working in VB.NET, so defining the { get; set; } part is a little more difficult to me, and I'm not entirely familiar with these constructs anyway. Is Customers a property? I was looking at them simply as variables of type IList using generics. – Mardis

Customers is a property -> yes. – Kronfeld

Check my latest edit - not sure if you know about this, it's just syntactic sugar but I can't seem to find any documentation about it and can't seem to figure it out. – Mardis

try creating a new SO post with the c# code and asking for someone to do it as VB. Eekk.. vb. poor thing. – Kronfeld

Ooh, I don't suppose you know of anywhere that actually states "there is no sync back!"? I need to provide it for documentation when providing the report to the other developers. (I feel as if I should be paying you! ;-) ) – Mardis

I know I've asked a lot of questions, but could you check the "This Must Be The Last Problem Or I Will Eat My Own Arm" section? :) – Mardis

A very worthy, detailed and comprehensive answer; I wish I could give more than +1 - it would be deserved. – Photogenic

Aye, I can see how that may be possible. I had dragged and dropped the sproc on, but now I've re-created the dbml with IMultipleResults as the return type. Check my update for more info. – Mardis

:) Good stuff Kezzer - glad to hear it's working. I really love this L2S stuff, when you get it working right! @ Marc: thanks heaps mate! Coming from you means a lot, to me :) thanks again. Mucho respecto :) – Kronfeld

Answer 2

Multiple Record Sets in ASP.NET MVC:

If you have stored procedure that return A and B. Then you create a specific ViewModel:

public class AB
{
  public A DataA { get; set; };
  public B DataB { get; set; };
}

You can also use the ViewData dictionary instead of the Model property (or in combination with this property which also works)

Strongly Typed Stored Procedure Output

You create a specific class for the results returning from the stored procedure with custom fields.

LINQ Column Lookups

Not 100% sure about this, but LINQ lookups the column field names and parameter names from a stored procedure at design time.

Acrid

Excellent. So for the first point I'm not entirely sure. Could you possible elaborate on this with a small example? Second point: how do you create this class? Just in the models folder? What should it look like? Third point: so lookups are performed every time it's accessed, or only once? – Mardis

Heh michael. While i was typing my reply, i saw that you posted yours (but didn't read it until after i posted). We followed the same formatting and similar replies :) get out of my head! :) :) – Kronfeld

Hi Kezzer, as you could see Pure.Krome has answered this better than me. But I can still answer if you want. :-) – Acrid