SubSonic Inner join Select problem

Question

Hi, I have 3 tables and need to select some recoreds, In 2 table of them I have fileds with same name , and when I try to use Where Expression on these filed I got error message : If I use TABLENAMe.Columns.COLNAME this error message shows : Ambiguous column name 'FKLoginID'. and if I use TableNAme.COLColumn.QualifiedName , it has error near created paramter "@[dbo].[Tbl_PersonalInformation].[FKLoginID]0"

How can I query on these tables? Thanks

 SqlQuery q = new Select().From(Tables.TblStockbrokerBroadDirector)
               .InnerJoin(TblPersonalInformation.PersonalInfoIDColumn, TblStockbrokerBroadDirector.FKPersonalInfoIDColumn)
               .InnerJoin(TblCompanyInformation.BizInfoIDColumn, TblStockbrokerBroadDirector.FKBizInfoIDColumn)
               .Where(TblPersonalInformation.FKLoginIDColumn.QualifiedName).IsEqualTo(User.Identity.Name);

CREATE TABLE [dbo].[Tbl_CompanyInformation](
    [Code] [bigint] IDENTITY(111111111,1) NOT NULL,
    [BizInfoID] [nvarchar](20)  NOT NULL,
    [BizName] [nvarchar](50)  NOT NULL,
    [RegisterationNO] [nvarchar](50)  NOT NULL,
    [RegisterationPlace] [bigint] NOT NULL,
    [TypeBiz] [nvarchar](50)  NOT NULL,
    [DirectManagerCode] [nvarchar](20)  NOT NULL,
    [FKAddressID] [nvarchar](20)  NOT NULL,
    [FKLoginID] [nvarchar](20)  NOT NULL,
    [SabtDate] [nvarchar](50)  NOT NULL,
    [NewName] [nvarchar](50)  NULL,
    [OldName] [nvarchar](50)  NULL,
    [DateTasisAgahi] [nvarchar](50)  NOT NULL,
    [NOTasisAgahi] [nvarchar](20)  NOT NULL,
    [NOAsasname] [nvarchar](20)  NOT NULL,
    [FKStatus] [smallint] NOT NULL CONSTRAINT [DF_Tbl_CompanyInformation_FKStatus]  DEFAULT ((0)),
 CONSTRAINT [PK_Tbl_CompanyInformation_1] PRIMARY KEY CLUSTERED 
(
    [Code] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [IX_Tbl_Biz] UNIQUE NONCLUSTERED 
(
    [BizInfoID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [IX_Tbl_CompanyRegNoP] UNIQUE NONCLUSTERED 
(
    [RegisterationNO] ASC,
    [RegisterationPlace] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Tbl_PersonalInformation](
    [Code] [bigint] IDENTITY(111111111,1) NOT NULL,
    [PersonalInfoID] [nvarchar](20)  NOT NULL,
    [FKLoginID] [nvarchar](20)  NULL,
    [FirstName] [nvarchar](50)  NOT NULL,
    [LastName] [nvarchar](150)  NOT NULL,
    [SSN] [nvarchar](10)  NOT NULL,
    [NationalCode] [nvarchar](10)  NOT NULL,
    [CopyNCard] [image] NULL,
    [Birthyear] [nvarchar](50)  NOT NULL,
    [Birthplace] [bigint] NOT NULL,
    [FKProvince] [smallint] NOT NULL,
    [FKAddressID] [nvarchar](20)  NOT NULL,
    [Phone] [nvarchar](50)  NULL,
    [Sex] [bit] NOT NULL CONSTRAINT [DF_Tbl_PersonalInformation_Sex]  DEFAULT ((0)),
    [FKStatus] [smallint] NULL CONSTRAINT [DF_Tbl_PersonalInformation_FKStatus]  DEFAULT ((0)),
 CONSTRAINT [PK_Tbl_PersonalInformation_1] PRIMARY KEY CLUSTERED 
(
    [Code] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [IX_Tbl_PersonalInformation] UNIQUE NONCLUSTERED 
(
    [PersonalInfoID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


CREATE TABLE [dbo].[Tbl_Stockbroker_BroadDirector](
    [Code] [bigint] IDENTITY(111111111,1) NOT NULL,
    [StockbrokerCode] [nvarchar](20)  NOT NULL,
    [FKBizInfoID] [nvarchar](20)  NOT NULL,
    [FKPersonalInfoID] [nvarchar](20)  NULL,
    [IsStockbroker] [bit] NOT NULL CONSTRAINT [DF_Tbl_Stockbroker_BroadDirector_IsStockbroker]  DEFAULT ((0)),
    [IsBoardDirector] [bit] NOT NULL CONSTRAINT [DF_Tbl_Stockbroker_BroadDirector_IsBoardDirector]  DEFAULT ((0)),
    [FKStatus] [smallint] NULL CONSTRAINT [DF_Tbl_Stockbroker_BroadDirector_status]  DEFAULT ((0)),
    [StockPercent] [float] NULL CONSTRAINT [DF_Tbl_Stockbroker_BroadDirector_StockPercent]  DEFAULT ((0)),
    [SahamdarHoghoghi] [bit] NULL,
    [FkBizinfoIDSahamdar] [nvarchar](20)  NULL,
 CONSTRAINT [PK_Tbl_Stockbroker_BroadDirector] PRIMARY KEY CLUSTERED 
(
    [Code] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [IX_Tbl_Stockbroker_Code] UNIQUE NONCLUSTERED 
(
    [StockbrokerCode] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Answer 1

The following should work:

SqlQuery query = DB.Select().From(Tables.TblStockbrokerBroadDirector)  
  .InnerJoin(TblPersonalInformation)  
  .InnerJoin(TblCompanyInformation)  
  .Where(TblPersonalInformation.Columns.FKLoginIDColumn).IsEqualTo(User.Identity.Name);

Answer 2

Change

.Where(TblPersonalInformation.FKLoginIDColumn.QualifiedName).IsEqualTo(User.Identity.Name);

to

.Where(TblPersonalInformation.FKLoginIDColumn).IsEqualTo(User.Identity.Name);

When you call .QualifiedName you are inadvertently calling Where(string columnName) instead of Where(TableColumn column).

You can verify this by checking the value of q.BuildSqlStatement(). This is very useful for debugging.

Answer 3

Thanks for your answer, but by yor code I got this error message

Object reference not set to an instance of an object. 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error: 


Line 33:                .InnerJoin(TblCompanyInformation.BizInfoIDColumn, TblStockbrokerBroadDirector.FKBizInfoIDColumn)
Line 34:                .Where(TblPersonalInformation.FKLoginIDColumn).IsEqualTo(User.Identity.Name);
Line 35:            Response.Write(q.BuildSqlStatement());
Line 36:             q.ExecuteDataSet();
Line 37:         }


Source File: E:\MehdiBackup\Visual Studio 2008\Projects\EXtjsUI\EXtjsUI\testi\default.aspx.cs    Line: 35 

Stack Trace: 


[NullReferenceException: Object reference not set to an instance of an object.]
   SubSonic.ANSISqlGenerator.BuildConstraintSQL(String& constraintOperator, StringBuilder sb, Boolean isFirst, Boolean& expressionIsOpen, Constraint c) +129
   SubSonic.ANSISqlGenerator.GenerateWhere() +218
   SubSonic.ANSISqlGenerator.BuildSelectStatement() +124
   SubSonic.SqlQuery.BuildSqlStatement() +71
   IMPermit.testi._default.Page_Load(Object sender, EventArgs e) in E:\MehdiBackup\Visual Studio 2008\Projects\EXtjsUI\EXtjsUI\testi\default.aspx.cs:35
   System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15
   System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +33
   System.Web.UI.Control.OnLoad(EventArgs e) +99
   System.Web.UI.Control.LoadRecursive() +47
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1436

Answer 4

I see that these query only return one tabble, how do you return all tables?

thank