Databases
StackOverflow2013
Postsdbo
POASP.NET MVC And LINQ General Questions
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POASP.NET MVC And LINQ General Questions
    primarykey
    data
    text
    <p>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.</p> <p><strong>Multiple Record Sets in ASP.NET MVC</strong></p> <p>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?</p> <p>In <a href="http://www.asp.net/learn/mvc/tutorial-11-vb.aspx" rel="noreferrer">this tutorial</a> we see how the data is retrieved. But it uses <code>ViewData.Model</code> which indicates a single result set, it doesn't explain what happens if multiple result sets are returned, or how to get them.</p> <p><strong>Strongly Typed Stored Procedure Output</strong></p> <p>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? </p> <p>In the previous section I described <a href="http://www.asp.net/learn/mvc/tutorial-11-vb.aspx" rel="noreferrer">this tutorial</a>, but this also only shows how to create LINQ to SQL for tables only, not the custom output from a sproc. </p> <p><strong>LINQ Column Lookups</strong></p> <p>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?</p> <p><strong>DBML Formats</strong></p> <p>Should *.dbml files incorporate all tables from a database? We have about 15 databases with many, many tables in each one.</p> <p><strong>A View For Every Output</strong></p> <p>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?</p> <p><strong>This Must Be The Last Problem Or I Will Eat My Own Arm</strong></p> <p><em>"Unable to cast object of type 'SingleResult`1[IntranetMVC.UserDetail]' to type 'IntranetMVC.UserDetail'."</em></p> <p>Here's the function:</p> <pre><code> 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 </code></pre> <p>It's occurring on all of the <code>userDetail, office, activeUser</code> and <code>dept</code> 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.</p> <p><strong>UPDATE</strong></p> <p>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:</p> <p><em>Unable to cast object of type 'SingleResult<code>1[IntranetMVC.Office]' to type 'System.Collections.Generic.IList</code>1</em></p> <pre><code>Imports System.Data.Linq Imports System.Reflection Imports System.Data.Linq.Mapping Partial Class PersonnelDataContext &lt;FunctionAttribute(Name:="dbo.UserDetailProc"), _ ResultType(GetType(UserDetail)), _ ResultType(GetType(IList(Of Office))), _ ResultType(GetType(IList(Of ActiveUser))), _ ResultType(GetType(IList(Of Department)))&gt; _ Public Function UserDetail( _ &lt;Parameter(Name:="User_Key", DbType:="Int")&gt; ByVal User_Key As Integer, _ &lt;Parameter(Name:="EditYN", DbType:="Char")&gt; 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 </code></pre> <p><strong>FIX</strong></p> <p>Okay, I didn't realise because to be honest with you I wasn't checking the return types correctly. I <em>assumed</em> 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.</p> <p>Thanks very much!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USKezzer
    1. USKezzer
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POASP.NET MVC And LINQ General Questions
      1. USKezzer
      1. VTFavorite
    2. VO
      singulars
      1. POASP.NET MVC And LINQ General Questions
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POASP.NET MVC And LINQ General Questions
      1. USMitch Baker
      1. VTFavorite
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload