Link to home
Start Free TrialLog in
Avatar of sagir
sagir

asked on

Stored procedures as Linq Entities

Hi,
I was wondering what is the right way to use Stored Procedures with Linq to Sql. I mean the case when the SP is not fit to some DB table which is already an entity. For instance it computes something as MAX, or AVG and returns it's value via SELECT. Then it wont fit no entity and this means it would return an
System.Data.Linq.ISingleResult<SP_SOME_PROCResult>
type.
This means that I can populate a DataRow with it but can't really use hard coded types which the SP returns.

Isn't there a way so that the Linq to SQL would make an entity based on the fields which the SP returns? It was possible with XSD datasets on previous versions (By just dragging the Proc to the XSD window). Can't this be done with Linq to SQL ?

Thanks.
Avatar of Fernando Soto
Fernando Soto
Flag of United States of America image

Can you post the SP in question please. Thanks
You can drag the SP to the LinqToSql designer. It will automatically create a type class for the resultset, just like you would have one for your tables.
Avatar of sagir
sagir

ASKER

PockyMaster
Ok and I can use it only by looping through the ISingleResult items ?
Isn't there a way to put the result into a single SP_PROCResult object?
Lets say my stored proc is: "SELECT Fname, Lname FROM Users WHERE Id = @someID"
then I use:
var myRes = dbCont.GetName(12);
foreach(SPPROCResult res in myRes)
{
...
}

But is it necessary to loop through results (When I know there could only be max one result)? Isn't there a way to put the results into a list or into a single SP_PROCResult from the beginning?

Thanks
Hi sagir;

Using your example on your last post you can do the following :

var myRes = dbCont.GetName(12).FirstOrDefault();

String FirstName = myRes.Fname;
String LastName = myRes.Lname;

No need to loop through if only one record or field is returned as shown above.

Fernando
Avatar of sagir

ASKER

Hi Fernando,

This wont work. I have a SP which is called SP_TEACHERMONTHSUM_GET_CURRENT. I try to do the following:
var myRes = dbDrLogy.SP_TEACHERMONTHSUM_GET_CURRENT(Convert.ToInt32(ddl_teacher_list.SelectedValue),
                                                            Convert.ToInt32(ddl_month.SelectedValue),
                                                            Convert.ToInt32(ddl_year.SelectedValue)).FirstOrDefault();
But it wont work. It says:
Error      20      'System.Data.Linq.ISingleResult<DAL.SP_TEACHERMONTHSUM_GET_CURRENTResult>' does not contain a definition for 'FirstOrDefault' and no extension method 'FirstOrDefault' accepting a first argument of type 'System.Data.Linq.ISingleResult<DAL.SP_TEACHERMONTHSUM_GET_CURRENTResult>' could be found (are you missing a using directive or an assembly reference?)      

What is wrong?
If it generates an ISingleResult, then it does not generate your entity class properly.
Make sure that it does not return any other results before your query inside your stored procedure. (e.g. use SET NOCOUNT ON)
And for the single result, do as Fernando suggested (SingleOrDefault) or e.g. create a wrapper class that does that for you.
Avatar of sagir

ASKER

You can see the SP itself below. I do have SET NOCOUNT ON; added. As you can see this SP wont match no table/view entity on my Linq to SQL so it is a self generated result on this Stored Procedure on Linq. What else can I do?

And SingleOrDefault wont work on the ISingleResult. It produces the same error as FirstOrDefault() like I showed before. Can you put some code based on my example which I can use?

Thanks
USE [dr_logy_co_il]
GO
/****** Object:  StoredProcedure [dbo].[SP_TEACHERMONTHSUM_GET_CURRENT]    Script Date: 09/04/2010 19:00:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SP_TEACHERMONTHSUM_GET_CURRENT]
	-- Add the parameters for the stored procedure here
	@TEACHER INT,
	@MONTH INT,
	@YEAR INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT     TEACMONTH_STATE, 
				TEACMONTH_TARIFF, 
				TEACMONTH_TARIFF_DRIVE, 
				TEACMONTH_TAX_METHOD, 
				TEACMONTH_TAX, 
				TEACMONTH_TAX_ADD, 
				TEACMONTH_TAX_PERC, 
				TEACMONTH_BONUS, 
				TEACMONTH_TAX_CLEAN,
				TEACMONTH_BALAL,
				TEACMONTH_BALAL_EMPLOYEE,
                ISNULL(TEACMONTH_SALARY, 0) AS TEACMONTH_SALARY,
                TEACMONTH_ISCHECKED,
                dbo.fnCountYearlyTaxLeftForTeacher(@TEACHER,@YEAR) AS NEW_TAX_LEFT
	FROM         TEACHER_MONTH_SUMS
	WHERE		(TEACMONTH_MONTH = @MONTH OR @MONTH IS NULL) AND 
				(TEACMONTH_YEAR = @YEAR OR @YEAR IS NULL) AND 
				(TEACMONTH_TEACHER = @TEACHER)
	ORDER BY TEACMONTH_YEAR DESC, TEACMONTH_MONTH DESC
END

Open in new window

Hi sagir;

In Solution Explorer on the DBML file expand it. Then right click on the file that end with .designer.cs and select view code. In that file you will find a function,

public ISingleResult<DAL.SP_TEACHERMONTHSUM_GET_CURRENTResult> SP_TEACHERMONTHSUM_GET_CURRENT( ...

and a class

public partial class DAL.SP_TEACHERMONTHSUM_GET_CURRENTResult

please post the complete function and class here.

Thanks;
Fernando
I've just created a test project:
Single table:

MyTable, with columns ID and SomeColumn.

Then created a proc:
CREATE PROCEDURE dbo.GetSomeData      AS
      SET NOCOUNT ON
      SELECT ID, SomeColumn  FROM MyTable
      RETURN

Dragged it onto the right panel of the LinqToSQL canvas:

It then created the following code:
 <Function Name="dbo.GetSomeData" Method="GetSomeData">
    <ElementType Name="GetSomeDataResult">
      <Column Name="ID" Type="System.Guid" DbType="UniqueIdentifier NOT NULL" CanBeNull="false" />
      <Column Name="SomeColumn" Type="System.String" DbType="NVarChar(50)" CanBeNull="true" />
    </ElementType>
  </Function>

Then when I want the First element (or none) I access it via:
   using (DataClasses1DataContext ctx = new DataClasses1DataContext())
            {
                var firsItem = ctx.GetSomeData().FirstOrDefault();
            }

That's all.
Avatar of sagir

ASKER

FernandoSoto:
function:

[global::System.Data.Linq.Mapping.FunctionAttribute(Name="dbo.SP_TEACHERMONTHSUM_GET_CURRENT")]
            public ISingleResult<SP_TEACHERMONTHSUM_GET_CURRENTResult> SP_TEACHERMONTHSUM_GET_CURRENT([global::System.Data.Linq.Mapping.ParameterAttribute(Name="TEACHER", DbType="Int")] System.Nullable<int> tEACHER, [global::System.Data.Linq.Mapping.ParameterAttribute(Name="MONTH", DbType="Int")] System.Nullable<int> mONTH, [global::System.Data.Linq.Mapping.ParameterAttribute(Name="YEAR", DbType="Int")] System.Nullable<int> yEAR)
            {
                  IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), tEACHER, mONTH, yEAR);
                  return ((ISingleResult<SP_TEACHERMONTHSUM_GET_CURRENTResult>)(result.ReturnValue));
            }

class:
public partial class SP_TEACHERMONTHSUM_GET_CURRENTResult
      {
            
            private System.Nullable<int> _TEACMONTH_STATE;
            
            private System.Nullable<decimal> _TEACMONTH_TARIFF;
            
            private System.Nullable<decimal> _TEACMONTH_TARIFF_DRIVE;
            
            private System.Nullable<int> _TEACMONTH_TAX_METHOD;
            
            private System.Nullable<decimal> _TEACMONTH_TAX;
            
            private System.Nullable<decimal> _TEACMONTH_TAX_ADD;
            
            private System.Nullable<decimal> _TEACMONTH_TAX_PERC;
            
            private System.Nullable<decimal> _TEACMONTH_BONUS;
            
            private System.Nullable<decimal> _TEACMONTH_TAX_CLEAN;
            
            private System.Nullable<decimal> _TEACMONTH_BALAL;
            
            private System.Nullable<decimal> _TEACMONTH_BALAL_EMPLOYEE;
            
            private decimal _TEACMONTH_SALARY;
            
            private System.Nullable<bool> _TEACMONTH_ISCHECKED;
            
            private System.Nullable<decimal> _NEW_TAX_LEFT;
            
            public SP_TEACHERMONTHSUM_GET_CURRENTResult()
            {
            }
            
            [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_TEACMONTH_STATE", DbType="Int")]
            public System.Nullable<int> TEACMONTH_STATE
            {
                  get
                  {
                        return this._TEACMONTH_STATE;
                  }
                  set
                  {
                        if ((this._TEACMONTH_STATE != value))
                        {
                              this._TEACMONTH_STATE = value;
                        }
                  }
            }
            
            [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_TEACMONTH_TARIFF", DbType="Decimal(18,2)")]
            public System.Nullable<decimal> TEACMONTH_TARIFF
            {
                  get
                  {
                        return this._TEACMONTH_TARIFF;
                  }
                  set
                  {
                        if ((this._TEACMONTH_TARIFF != value))
                        {
                              this._TEACMONTH_TARIFF = value;
                        }
                  }
            }
            
            [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_TEACMONTH_TARIFF_DRIVE", DbType="Decimal(18,2)")]
            public System.Nullable<decimal> TEACMONTH_TARIFF_DRIVE
            {
                  get
                  {
                        return this._TEACMONTH_TARIFF_DRIVE;
                  }
                  set
                  {
                        if ((this._TEACMONTH_TARIFF_DRIVE != value))
                        {
                              this._TEACMONTH_TARIFF_DRIVE = value;
                        }
                  }
            }
            
            [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_TEACMONTH_TAX_METHOD", DbType="Int")]
            public System.Nullable<int> TEACMONTH_TAX_METHOD
            {
                  get
                  {
                        return this._TEACMONTH_TAX_METHOD;
                  }
                  set
                  {
                        if ((this._TEACMONTH_TAX_METHOD != value))
                        {
                              this._TEACMONTH_TAX_METHOD = value;
                        }
                  }
            }
            
            [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_TEACMONTH_TAX", DbType="Decimal(18,2)")]
            public System.Nullable<decimal> TEACMONTH_TAX
            {
                  get
                  {
                        return this._TEACMONTH_TAX;
                  }
                  set
                  {
                        if ((this._TEACMONTH_TAX != value))
                        {
                              this._TEACMONTH_TAX = value;
                        }
                  }
            }
            
            [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_TEACMONTH_TAX_ADD", DbType="Decimal(18,2)")]
            public System.Nullable<decimal> TEACMONTH_TAX_ADD
            {
                  get
                  {
                        return this._TEACMONTH_TAX_ADD;
                  }
                  set
                  {
                        if ((this._TEACMONTH_TAX_ADD != value))
                        {
                              this._TEACMONTH_TAX_ADD = value;
                        }
                  }
            }
            
            [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_TEACMONTH_TAX_PERC", DbType="Decimal(18,2)")]
            public System.Nullable<decimal> TEACMONTH_TAX_PERC
            {
                  get
                  {
                        return this._TEACMONTH_TAX_PERC;
                  }
                  set
                  {
                        if ((this._TEACMONTH_TAX_PERC != value))
                        {
                              this._TEACMONTH_TAX_PERC = value;
                        }
                  }
            }
            
            [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_TEACMONTH_BONUS", DbType="Decimal(18,2)")]
            public System.Nullable<decimal> TEACMONTH_BONUS
            {
                  get
                  {
                        return this._TEACMONTH_BONUS;
                  }
                  set
                  {
                        if ((this._TEACMONTH_BONUS != value))
                        {
                              this._TEACMONTH_BONUS = value;
                        }
                  }
            }
            
            [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_TEACMONTH_TAX_CLEAN", DbType="Decimal(18,2)")]
            public System.Nullable<decimal> TEACMONTH_TAX_CLEAN
            {
                  get
                  {
                        return this._TEACMONTH_TAX_CLEAN;
                  }
                  set
                  {
                        if ((this._TEACMONTH_TAX_CLEAN != value))
                        {
                              this._TEACMONTH_TAX_CLEAN = value;
                        }
                  }
            }
            
            [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_TEACMONTH_BALAL", DbType="Decimal(18,2)")]
            public System.Nullable<decimal> TEACMONTH_BALAL
            {
                  get
                  {
                        return this._TEACMONTH_BALAL;
                  }
                  set
                  {
                        if ((this._TEACMONTH_BALAL != value))
                        {
                              this._TEACMONTH_BALAL = value;
                        }
                  }
            }
            
            [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_TEACMONTH_BALAL_EMPLOYEE", DbType="Decimal(18,2)")]
            public System.Nullable<decimal> TEACMONTH_BALAL_EMPLOYEE
            {
                  get
                  {
                        return this._TEACMONTH_BALAL_EMPLOYEE;
                  }
                  set
                  {
                        if ((this._TEACMONTH_BALAL_EMPLOYEE != value))
                        {
                              this._TEACMONTH_BALAL_EMPLOYEE = value;
                        }
                  }
            }
            
            [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_TEACMONTH_SALARY", DbType="Decimal(18,2) NOT NULL")]
            public decimal TEACMONTH_SALARY
            {
                  get
                  {
                        return this._TEACMONTH_SALARY;
                  }
                  set
                  {
                        if ((this._TEACMONTH_SALARY != value))
                        {
                              this._TEACMONTH_SALARY = value;
                        }
                  }
            }
            
            [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_TEACMONTH_ISCHECKED", DbType="Bit")]
            public System.Nullable<bool> TEACMONTH_ISCHECKED
            {
                  get
                  {
                        return this._TEACMONTH_ISCHECKED;
                  }
                  set
                  {
                        if ((this._TEACMONTH_ISCHECKED != value))
                        {
                              this._TEACMONTH_ISCHECKED = value;
                        }
                  }
            }
            
            [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_NEW_TAX_LEFT", DbType="Decimal(18,2)")]
            public System.Nullable<decimal> NEW_TAX_LEFT
            {
                  get
                  {
                        return this._NEW_TAX_LEFT;
                  }
                  set
                  {
                        if ((this._NEW_TAX_LEFT != value))
                        {
                              this._NEW_TAX_LEFT = value;
                        }
                  }
            }
      }
Avatar of sagir

ASKER

PockyMaster:
I did the same things, and the type created is:
<Function Name="dbo.SP_TEACHERMONTHSUM_GET_CURRENT" Method="SP_TEACHERMONTHSUM_GET_CURRENT">
    <Parameter Name="TEACHER" Parameter="tEACHER" Type="System.Int32" DbType="Int" />
    <Parameter Name="MONTH" Parameter="mONTH" Type="System.Int32" DbType="Int" />
    <Parameter Name="YEAR" Parameter="yEAR" Type="System.Int32" DbType="Int" />
    <ElementType Name="SP_TEACHERMONTHSUM_GET_CURRENTResult">
      <Column Name="TEACMONTH_STATE" Type="System.Int32" DbType="Int" CanBeNull="true" />
      <Column Name="TEACMONTH_TARIFF" Type="System.Decimal" DbType="Decimal(18,2)" CanBeNull="true" />
      <Column Name="TEACMONTH_TARIFF_DRIVE" Type="System.Decimal" DbType="Decimal(18,2)" CanBeNull="true" />
      <Column Name="TEACMONTH_TAX_METHOD" Type="System.Int32" DbType="Int" CanBeNull="true" />
      <Column Name="TEACMONTH_TAX" Type="System.Decimal" DbType="Decimal(18,2)" CanBeNull="true" />
      <Column Name="TEACMONTH_TAX_ADD" Type="System.Decimal" DbType="Decimal(18,2)" CanBeNull="true" />
      <Column Name="TEACMONTH_TAX_PERC" Type="System.Decimal" DbType="Decimal(18,2)" CanBeNull="true" />
      <Column Name="TEACMONTH_BONUS" Type="System.Decimal" DbType="Decimal(18,2)" CanBeNull="true" />
      <Column Name="TEACMONTH_TAX_CLEAN" Type="System.Decimal" DbType="Decimal(18,2)" CanBeNull="true" />
      <Column Name="TEACMONTH_BALAL" Type="System.Decimal" DbType="Decimal(18,2)" CanBeNull="true" />
      <Column Name="TEACMONTH_BALAL_EMPLOYEE" Type="System.Decimal" DbType="Decimal(18,2)" CanBeNull="true" />
      <Column Name="TEACMONTH_SALARY" Type="System.Decimal" DbType="Decimal(18,2) NOT NULL" CanBeNull="false" />
      <Column Name="TEACMONTH_ISCHECKED" Type="System.Boolean" DbType="Bit" CanBeNull="true" />
      <Column Name="NEW_TAX_LEFT" Type="System.Decimal" DbType="Decimal(18,2)" CanBeNull="true" />
    </ElementType>
  </Function>

But when I try to do this on my code:
var myRes = dbDrLogy.SP_TEACHERMONTHSUM_GET_CURRENT(Convert.ToInt32(ddl_teacher_list.SelectedValue),
                                                            Convert.ToInt32(ddl_month.SelectedValue),
                                                            Convert.ToInt32(ddl_year.SelectedValue)).FirstOrDefault();
It just wont let me. With the error I posted before. This is dbDrLogy:
/// <summary>
    /// Contains the linq entity of the database
    /// </summary>
    protected drlogyDataContext _dbDrLogy = null;
    public drlogyDataContext dbDrLogy
    {
        get
        {
            if (_dbDrLogy == null)
            {
                _dbDrLogy = new drlogyDataContext();
            }
            return _dbDrLogy;
        }
    }

What could be the problem? What am I doint wrong?
ASKER CERTIFIED SOLUTION
Avatar of PockyMaster
PockyMaster
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sagir

ASKER

LOL... did not expect this...
Works great now. Thanks :)
So what code did you end up using to answer the original question, "OK and I can use it only by looping through the ISingleResult items ? Isn't there a way to put the result into a single SP_PROCResult object?".

I posted the solution:
var myRes = dbCont.GetName(12).FirstOrDefault();

Which should have given you the solution you wanted? And if you used a method like FirstOrDefault() should have split the points?

You're welcome, it's always a pitfall, since it not that obvious :D
Avatar of sagir

ASKER

FernandoSoto: Thanks a lot for the help but the REAL problem was that I just forgot to import System.Linq , and Pocky suggested that one to me. I thought that since it is appearing in Intellisense then everything is ok with the imports but I was wrong.

Again, thanks for the help but final solution came from Pocky.