Solved

Stored procedures as Linq Entities

Posted on 2010-09-03
17
1,245 Views
Last Modified: 2013-11-11
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.
0
Comment
Question by:sagir
  • 7
  • 6
  • 4
17 Comments
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 33601745
Can you post the SP in question please. Thanks
0
 
LVL 14

Expert Comment

by:PockyMaster
ID: 33602306
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.
0
 
LVL 3

Author Comment

by:sagir
ID: 33602478
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
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 33603148
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
0
 
LVL 3

Author Comment

by:sagir
ID: 33603363
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?
0
 
LVL 14

Expert Comment

by:PockyMaster
ID: 33603483
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)
0
 
LVL 14

Expert Comment

by:PockyMaster
ID: 33603492
And for the single result, do as Fernando suggested (SingleOrDefault) or e.g. create a wrapper class that does that for you.
0
 
LVL 3

Author Comment

by:sagir
ID: 33603723
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

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 62

Expert Comment

by:Fernando Soto
ID: 33604137
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
0
 
LVL 14

Expert Comment

by:PockyMaster
ID: 33604178
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.
0
 
LVL 3

Author Comment

by:sagir
ID: 33604793
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;
                        }
                  }
            }
      }
0
 
LVL 3

Author Comment

by:sagir
ID: 33604801
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?
0
 
LVL 14

Accepted Solution

by:
PockyMaster earned 500 total points
ID: 33604839
You have imported the System.Linq namespace right?
Otherwise you will not get the extension methods (just a silly question)
0
 
LVL 3

Author Comment

by:sagir
ID: 33604901
LOL... did not expect this...
Works great now. Thanks :)
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 33605105
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?

0
 
LVL 14

Expert Comment

by:PockyMaster
ID: 33605770
You're welcome, it's always a pitfall, since it not that obvious :D
0
 
LVL 3

Author Comment

by:sagir
ID: 33606046
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.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now