none
Stored Proc with multiple joins RRS feed

  • Question

  • Have an existing sproc:

    CREATE PROCEDURE [dbo].[GetBuilderDetails] (@theNumber varchar(20))
     AS

    SET NOCOUNT ON;

    SELECT distinct x.*, z.StatusFullDesc, w.orgdesc
    FROM BUILDER x, BuilderStatusCodes z, BuilderOrgType w
    WHERE (x.BuilderNumber =  @theNumber)
    and (x.STATUS = z.Statuscode)
    and (x.ORGTYPE = w.orgcode)

    ===================

    Data Context creates:

    [Function(Name="dbo.GetBuilderDetails")]
            public ISingleResult<Phoenix.BEL.GetBuilderDetailsResult> GetBuilderDetails([Parameter(DbType="VarChar(20)")] string theNumber)
            {
                IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), theNumber);
                return ((ISingleResult<Phoenix.BEL.GetBuilderDetailsResult>)(result.ReturnValue));
            }

    ====================

    Data Adapter :

    public static BUILDER AllBuilderDetails(string Buildernumber)
            {
                using (PhoenixDataClassesDataContext db = new PhoenixDataClassesDataContext())
                {
                    var returnBuilder = db.GetBuilderDetails(Buildernumber).Single<GetBuilderDetailsResult>();
                    return new BUILDER()
                    {
                        NAME = returnBuilder.BuilderNumber,
                        CHARTER = returnBuilder.CHARTER,
                        STATE = returnBuilder.STATE
                    };
                }
           
            }

    =============

    I cannot see the fields ( z.StatusFullDesc, w.orgdesc ) returned from joined tables in result set.  Shouldn't I see "returnBuilder.StatusFullDesc"?


    Looking for some advice / direction.

    Thx
    -Al
    Sunday, March 7, 2010 1:42 PM

All replies

  • What happens when you execute the SPROC in SQL Server Studio? Is there a column there or is this a Linq problem?
    William Wegerson (www.OmegaCoder.Com)
    Monday, March 8, 2010 5:39 PM
    Moderator
  • When I execute the sproc the results list contains every field from the list ( x.*, z.StatusFullDesc, w.orgdesc ).  When I execute it through Linq as a method call I only see results from 1 table ( BUILDER x ). Could it be that my return value ( public static BUILDER ) is a "Builder" object?  Sorry for the newbie-style questions, this is my first go-around with Linq and sprocs.

    Thanks.

    -Al
    Tuesday, March 9, 2010 10:45 AM
  • Can you please include the definition for GetBuilderDetailsResult?
    Friday, April 2, 2010 2:25 PM
  • How did you import the stored procedure into your data context?  Did you just drag it from the server explorer to the stored procedure pane of the dbml editor, or did you use SqlMetal?
    Friday, April 2, 2010 5:13 PM