Missing schema in DBML when using LINQ to SP and Sp returning multiple recordsets

Question

when creating POC LINQ to SQL and entities, I ran into a problem stuck in a frozen deadlock. The problem is that I use LINQ to SP, and everything works fine, and I made cool editing, adding and removing methods. Then, a thing clicks in my mine that “what if I just return two recordsets from SP”. I did SP and returned two recordsets from it

SPs look like this: [Demo]

Create PROCEDURE [dbo].GetUserData

    @UserId Bigint

AS
BEGIN
    SET NOCOUNT ON;

-- Getting User
select * from [User] where id=@UserId
-- Getting User role
select * from [Role] where userId=@UserId
end

then I dropped this SP in my DBML (Linq to SQL) class and then noticed that only a single record set schema was created like this

<?xml version="1.0" encoding="utf-8"?>
<Database Name="MyInventory" Class="MyDBMLDataContext" xmlns="http://schemas.microsoft.com/linqtosql/dbml/2007">
  <Connection Mode="AppSettings" ConnectionString="Data Source=MyDatabaseServer\;Initial Catalog=MyInventory;Integrated Security=True" SettingsObjectName="ConsoleApplication16.Properties.Settings" SettingsPropertyName="MyInventoryConnectionString" Provider="System.Data.SqlClient" />
  <Function Name="dbo.GetUserData" Method="GetUserData">
    <Parameter Name="UserId" Parameter="userId" Type="System.Int64" DbType="BigInt" />
    <ElementType Name="GetUserDataResult">
      <Column Name="Id" Type="System.Int64" DbType="BigInt NOT NULL" CanBeNull="false" />
      <Column Name="Name" Type="System.String" DbType="VarChar(50) NOT NULL" CanBeNull="false" />
      <Column Name="Email" Type="System.String" DbType="NVarChar(50)" CanBeNull="true" />
      <Column Name="IsDeleted" Type="System.Boolean" DbType="Bit NOT NULL" CanBeNull="false" />
      <Column Name="HomePage" Type="System.String" DbType="VarChar(100)" CanBeNull="true" />
    </ElementType>
  </Function>
</Database>

I clearly see that only one set of entries is created from user records, and he lacks the role scheme: (.

- , ?

Lura

Answer 1

- -. DatabaseExtensions.cs .

, -

public partial class DataBaseDataContext
{
    [ResultType(typeof(FirstResult))]
    [ResultType(typeof(SecondResult))]
    [Function(Name = "dbo.StoredProc")]
    public IMultipleResults StoredProc([global::System.Data.Linq.Mapping.ParameterAttribute(DbType = "Int")] System.Nullable<System.Int> ID)
    {
        IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), ID);
        return ((IMultipleResults)(result.ReturnValue));
    }
}

public class FirstResult;

public class SecondResult;

. , , , .

FirstResult SecondResult - . dbml, .cs, . , .

DataBaseDataContext dataCon = new DataBaseDataContext();
var results = dataCon.StoredProc(id);
var firstSet = results.GetResult<FirstResult>();
var secondSet = results.GetResult<SecondResult>();
//process data

, , . , , LINQ - , .

Answer 2

, ? . .

Answer 3

, dbml , , , LinqToSql .

((System.Data.Linq.SqlClient.ObjectReaderCompiler.ObjectReaderSession
    <System.Data.SqlClient.SqlDataReader>)
   (((System.Data.Linq.SqlClient.SqlProvider.ExecuteResult)(result)).session)).buffer

buffer[0], select, buffer[1] . , IEnumerable IEnumerable<GetAllResult> IEnumerable<GetAllResult1>, .

dbml

<Function Name="dbo.GetAll" Method="GetAll">
    <ElementType Name="GetAllResult">
      <Column Name="ID" Type="System.Int32" DbType="Int" CanBeNull="true" />
      <Column Name="Tagname" Type="System.String" DbType="NChar(10)" CanBeNull="true" />
    </ElementType>
    <ElementType Name="GetAllResult1">
      <Column Name="Id" Type="System.Int32" DbType="Int" CanBeNull="true" />
      <Column Name="TagId" Type="System.Int32" DbType="Int" CanBeNull="true" />
      <Column Name="Name1" Type="System.String" DbType="NChar(10)" CanBeNull="true" />
    </ElementType>
  </Function>

cs

[global::System.Data.Linq.Mapping.FunctionAttribute(Name="dbo.GetAll")]
[global::System.Data.Linq.Mapping.ResultTypeAttribute(typeof(GetAllResult))]
[global::System.Data.Linq.Mapping.ResultTypeAttribute(typeof(GetAllResult1))]
public IMultipleResults GetAll()
{
   IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
   return ((IMultipleResults)(result.ReturnValue));
}