C# – LINQ to SQL: Stored Procedure Results

clinqlinq-to-sqlsqltsql

How can I change the class name of stored procedure result generated by LINQ to SQL designer (besides messing with designer.cs)?

Also, how can you perform a linq query on the result set of the stored procedure?

Best Answer

Can you edit this in the dbml? Personally, I tend to treat the auto-generated types (from functions and stored procedures) as DTOs that are local to the DAL, so I immediately re-map them to my own POCO representation - i.e.

var qry = from row in ctx.SomeProc(12345)
          select new Foo {ID = row.ID, Name = row.Name };

etc. Re the second question "Also, how can you perform a linq query on the result set of the stored procedure?" - I would recommend using a UDF instead of a stored procedure if you want to compose it: this allows you to do the composition at the database, for example paging and filtering:

var qry = (from row in ctx.SomeFunction(12345)
          where row.IsActive
          select row).Skip(10).Take(10);

which should (in LINQ-to-SQL at least) do everything in TSQL at the server. Otherwise, you can call AsEnumerable() and use LINQ-to-Objects at the calling .NET layer:

var qry = (from row in ctx.SomeProc(12345).AsEnumerable()
          where row.IsActive
          select row).Skip(10).Take(10);

To edit the dbml (which is just xml), change the ElementType/@Name here:

<Function Name="dbo.CustOrderHist" Method="CustOrderHist">
  <Parameter Name="CustomerID" Parameter="customerID" Type="System.String" DbType="NChar(5)" />
  <ElementType Name="FooBar"> <!-- ********** HERE ************ -->
      <Column Name="ProductName" Type="System.String" DbType="NVarChar(40) NOT NULL" CanBeNull="false" />
      <Column Name="Total" Type="System.Int32" DbType="Int" CanBeNull="true" />
  </ElementType>
</Function>
Related Topic