Simple Join

Question

Hi, This question is about subsonic

I am trying to build a select query HAVING joins in it using subsonic.

For Example if I want to extract data from 3 tables then how I will be able to do it in Subsonic. Lets say if I have a TSQL given below then How I will be able to translate this into Subsonic?

Select la.LoanAppId, ci.FirstName, ci.LastName, la.ApplicationDateSubmitted, 
       la.LoanAmount, la.DueDate, lkUpD.Col1Value
from LoanApplication la, ContactInfo ci, LookUpDetails lkUpD
where la.UserId = ci.UserId
and la.StatusId =   lkUpD.LookUpDetailId

Please Reply

Answer 1

SubSonic.SqlQuery q = new Select(
     LoanApplication.LoanAppId, ContactInfo.FirstNa, [etc])
    .From(LoanApplication.Schema)
    .InnerJoin(ContactInfo.Schema)
    .InnerJoin(LookUpDetails.Schema);

if all the inner joins are natural (else, you need to add more .InnerJoin calls to specify the inner-join conditions).

Answer 2

Hi, Thanks for you response but there were few problems I was facing then I made few changes as follows.

SubSonic.Query.SqlQuery q = new Select()
    .From (LoanApplicationTable)()
    .InnerJoin (ContactInfoTable)()
    .InnerJoin (LookUpDetailsTable)()
    .Where(LoanApplicationTable.UserId).IsEqualTo(ContactInfoTable.UserId)
    .And
    (LoanApplicationTable.StatusId).IsEqualTo (LookUpDetailsTable.LookUpDetailId)

I get the following error

The schema hasn't been loaded by this provider. This is usually done by the constructor of the 'QuerySurface' or 'DataContext' provided by the template you're using. Make sure to use it, and not the query directly

Please help