Simple SQL returns IMultipleResults?

Discussion of open issues, suggestions and bugs regarding LinqConnect – Devart's LINQ to SQL compatible ORM
Post Reply
Mojo
Posts: 2
Joined: Thu 12 Dec 2019 11:44

Simple SQL returns IMultipleResults?

Post by Mojo » Thu 12 Dec 2019 11:51

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
Site Admin
Posts: 8994
Joined: Thu 14 Aug 2008 12:44

Re: Simple SQL returns IMultipleResults?

Post by Shalex » Thu 26 Dec 2019 16:03

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
Site Admin
Posts: 8994
Joined: Thu 14 Aug 2008 12:44

Re: Simple SQL returns IMultipleResults?

Post by Shalex » Mon 20 Jan 2020 19:45

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
Posts: 2
Joined: Thu 12 Dec 2019 11:44

Re: Simple SQL returns IMultipleResults?

Post by Mojo » Tue 21 Jan 2020 10:17

But using Microsoft LinqToSql only returns only a single set?

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

Shalex
Site Admin
Posts: 8994
Joined: Thu 14 Aug 2008 12:44

Re: Simple SQL returns IMultipleResults?

Post by Shalex » Tue 21 Jan 2020 12:31

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.

Post Reply