QuerySpec WhereExist

Posts   
 
    
morten71
User
Posts: 80
Joined: 13-Jan-2009
# Posted on: 28-Nov-2012 12:30:11   

Using QuerySpec I'm trying to construct the following sql:

SELECT .* dbo.ReportFiles rf INNER JOIN dbo.CampaignReports cr ON rf.CampaignReportId = cr.CampaignReportId INNER JOIN dbo.CampaignLocations cl ON cr.CampaignLocationId = cl.CampaignLocationId INNER JOIN dbo.Campaigns ca ON cl.CampaignId = ca.CampaignId WHERE ca.CustomerId = @customerId AND ca.CampaignId in (123, 456, 789) AND cr.ReportDate BETWEEN @periodFrom AND @periodTo AND EXISTS (SELECT * FROM dbo.ReportFileTags rft WHERE rft.ReportFileId = rf.ReportFileId AND rft.FileTag = @tag)

I can't figure out how to get the WhereExists right and I can't find any examples.

Here is how far I got in VB:

Dim qf As New QueryFactory() Dim q = qf.ReportFiles.From( _ QueryTarget _ .InnerJoin(CampaignReportsEntity.Relations.ReportFilesEntityUsingCampaignReportId) _ .InnerJoin(CampaignLocationsEntity.Relations.CampaignReportsEntityUsingCampaignLocationId) _ .InnerJoin(CampaignsEntity.Relations.CampaignLocationsEntityUsingCampaignId)) _ .Where( _ (CampaignsFields.CustomerId = lCustomerId) _ And (CampaignsFields.CampaignId.In(lLstOfCampaignIds)) _ And (CampaignReportsFields.ReportDate.Between(lPeriodFrom, lPeriodTo)))

Can anyone help?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 28-Nov-2012 13:47:20   

Did you try the .Any() extension method? .Any() will be converted into an exists query.

So:

... .AndWhere(_queryInsideExist_.Any())

Frans Bouma | Lead developer LLBLGen Pro
morten71
User
Posts: 80
Joined: 13-Jan-2009
# Posted on: 28-Nov-2012 15:42:22   

Otis wrote:

Did you try the .Any() extension method? .Any() will be converted into an exists query.

So:

... .AndWhere(_queryInsideExist_.Any())

thanks for your reply.

I solved it using IN:

q.Where(ReportFilesFields.ReportFileId.In(qf.Create().Select(ReportFileTagsFields.ReportFileId).Where(ReportFileTagsFields.FileTag = lTag)))