Simple SQL returns IMultipleResults?

[Mojo]

Hi

I have this TSQL code...

Code: Select all

ALTER PROCEDURE [dbo].[PROC_Præmier_script_hent_perioder] 
	@gruppeID UNIQUEIDENTIFIER,
	@policetypeID UNIQUEIDENTIFIER,
	@fællespoliceID UNIQUEIDENTIFIER
AS
BEGIN
	SET NOCOUNT ON;

	
	IF @fællespoliceID IS NULL
	BEGIN
		SELECT        DISTINCT periode_fra, periode_til
		FROM          dbo.TTYP_Præmier_script
		WHERE         (slettet_dato IS NULL) AND 
					  (gruppe_ID=@gruppeID) AND 
					  (policetype_ID=@policetypeID) AND 
					  (fællespolice_ID IS NULL)
		ORDER BY	  periode_til DESC
	END
	ELSE
	BEGIN
		SELECT        DISTINCT periode_fra, periode_til
		FROM          dbo.TTYP_Præmier_script
		WHERE         (slettet_dato IS NULL) AND 
					  (fællespolice_ID=@fællespoliceID)
		ORDER BY	  periode_til DESC
	END

END

And LinqConnect returns a multiple resultsset...

Code: Select all

<FunctionAttribute(Name:="dbo.PROC_Præmier_script_hent_perioder")>
        <ResultType(GetType(PROC_Præmier_script_hent_perioderResult))>
        <ResultType(GetType(PROC_Præmier_script_hent_perioderResult1))>
        Public Function PROC_Præmier_script_hent_perioder(<Parameter(Name:="gruppeID", DbType:="UNIQUEIDENTIFIER")> gruppeID As System.Guid?, <Parameter(Name:="policetypeID", DbType:="UNIQUEIDENTIFIER")> policetypeID As System.Guid?, <Parameter(Name:="fællespoliceID", DbType:="UNIQUEIDENTIFIER")> fællespoliceID As System.Guid?) As System.Data.Linq.IMultipleResults
            Dim _PROC_Præmier_script_hent_perioderResult As IExecuteResult = Me.ExecuteMethodCall(Me, CType(MethodInfo.GetCurrentMethod(), MethodInfo), gruppeID, policetypeID, fællespoliceID)
            Return CType(_PROC_Præmier_script_hent_perioderResult.ReturnValue, System.Data.Linq.IMultipleResults)
        End Function

Why is it returning multiple resultsset?

[Shalex]

We have reproduced the generation of IMultipleResults by Entity Developer for your stored procedure. We will investigate the case and notify you about the result.

[Shalex]

This behavior of Entity Developer is caused by the way how System.Data.SqlClient retrieves metadata. The provider returns the first schema table, then NextResult() returns True and gives the second schema table.

As a workaround with Entity Developer for your case, please open Model Explorer, navigate to Method Editor and remove redundant Value Types - leave the only one to generate ISingleResult.

[Mojo]

But using Microsoft LinqToSql only returns only a single set?

Your "workaround" will cause lots of headaches when recreating the model.

[Shalex]

1. This behavior of Entity Developer is caused by the way how System.Data.SqlClient retrieves metadata. The provider returns the first schema table, then NextResult() returns True and gives the second schema table:

Code: Select all

      var connection = new SqlConnection(@"Data Source=dbmssqlx64\mssql2016;  Initial Catalog=test;  Persist Security Info=True; User ID=sa;");
      connection.Open();

      var cmd = connection.CreateCommand();
      cmd.CommandType = System.Data.CommandType.StoredProcedure;
      cmd.CommandText = "[dbo].[PROC_Præmier_script_hent_perioder]";
      SqlCommandBuilder.DeriveParameters(cmd);

      var allSchemaTables = new List<DataTable>();
      using (var reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly)) {
        do {
          var schemaTable = reader.GetSchemaTable();
          allSchemaTables.Add(schemaTable);
        }
        while (reader.NextResult());
      }

2. The behavior of a standard LINQ to SQL designer suits your scenario but it handles incorrectly the case when stored procedure returns several result sets:

Code: Select all

create PROCEDURE [dbo].[PROC_Præmier_script_hent_perioder_3]
@gruppeID UNIQUEIDENTIFIER,
@policetypeID UNIQUEIDENTIFIER,
@fællespoliceID UNIQUEIDENTIFIER
AS
BEGIN
SET NOCOUNT ON;
SELECT * from dept where deptno < 20;
SELECT * from dept where deptno = 20;
SELECT * from dept where deptno > 20;
END

The standard LINQ to SQL designer generates ISingleResult although the stored procedure always (!) returns 3 result sets. Entity Developer generates IMulitpleResults.

3. As a workaround with Entity Developer for your case, please open Model Explorer, navigate to Method Editor and remove redundant Value Types - leave the only one to generate ISingleResult.