Hi there, I want to create the below query using the QueryFactory object:

SELECT * FROM Table1    
WHERE  

     Table1.Field1 = 13 
    AND 
    (
        EXISTS (
                SELECT Table2.Id 
                FROM Table2
                INNER JOIN Table3  ON Table2.Id = Table3.Table2Id
                WHERE Table3.Field1 IN (0,1,2) AND Table2.Table1Id = Table1.Id
                )
    OR NOT EXISTS(
                SELECT Table2.Id 
                FROM Table2
                WHERE Table2.Table1Id = Table1.Id
    )

    )

But I don't know how to add the second NOT EXISTS sub query. Can you help me to write this query?

Thanks in advance

Hiheerser,

Quoting the documentation:

Exists/NotExists/Any/All Any and All, two Linq query operators are directly related to Exists/Not Exists in SQL. To produce an All() query, the developer can simply call .All(predicate) and for an any call, simply call .Any(predicate). These methods wrap a call to Functions.Exists/NotExists. The All(predicate) method produces a NOT EXISTS(NOT PREDICATE) predicate. Any simply produces EXISTS(PREDICATE)

Also, you can concatenate those two predicates using predicate.Or(predicate) construct. (ref...)

If you still have problems with that please post what you have so far (code) and more information like the LLBLGen Runtime Library version, etc ( http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7722 ).

Hi David,

This is what I have:

  var qf = new QueryFactory();
  var q = qf.Table1.Where(Table1Fields.Field1 = 14);

    q.WhereExists(qf.Table2
      .CorrelatedOver(Table2Fields.Table1Id == Table1Fields.Id)
      .From(QueryTarget.InnerJoin(qf.Table3).On(Table3Fields.Table2Id == Table2Fields.Id))
      .Where(Table3Fields.Field2 = 12)
      .Select(Table2Fields.Table1Id));

But then I got kind of stuck grt

This might give you a hint, just to prove the syntax.

var q = qf.Create().Select(CustomerFields.ContactName).Where(
    qf.Order.CorrelatedOver(OrderFields.CustomerId == CustomerFields.CustomerId).Where(OrderFields.EmployeeId == 1).Any()
    )
    .OrWhere(
    qf.Order.CorrelatedOver(OrderFields.CustomerId == CustomerFields.CustomerId).Where(OrderFields.ShipVia == 1).Any());

Thx Marvin! The All method requires a predicate so this:

        q.AndWhere(
          (
          qf.Deelplan
            .CorrelatedOver(DeelplanFields.PlanId == PlanFields.Id)
            .From(QueryTarget.InnerJoin(qf.Variant).On(VariantFields.DeelplanId == DeelplanFields.Id))
            .Where(variantFilter).Select(DeelplanFields.PlanId).Any()
            ).Or(
            qf.Deelplan
            .CorrelatedOver(DeelplanFields.PlanId == PlanFields.Id)
            .Select(DeelplanFields.PlanId).All(DeelplanFields.PlanId == DeelplanFields.PlanId) //don't know what do add here...
            )
        );

produces the following:

OR NOT  EXISTS (SELECT [dbo].[Deelplan].[PlanID] AS [PlanId] FROM [dbo].[Deelplan]   WHERE ( ( NOT [dbo].[Deelplan].[PlanID] = [dbo].[Deelplan].[PlanID]) AND ( [dbo].[Deelplan].[PlanID] = [dbo].[Plan].[ID])))

So to get ride of the NOT [dbo].[Deelplan].[PlanID] = [dbo].[Deelplan].[PlanID]

I changed it to this:

    q.AndWhere(
      (
      qf.Deelplan
        .CorrelatedOver(DeelplanFields.PlanId == PlanFields.Id)
        .From(QueryTarget.InnerJoin(qf.Variant).On(VariantFields.DeelplanId == DeelplanFields.Id))
        .Where(variantFilter).Select(DeelplanFields.PlanId).Any()

        ).OrNot(
        qf.Deelplan
        .CorrelatedOver(DeelplanFields.PlanId == PlanFields.Id)
        .Select(DeelplanFields.PlanId).Any()
        )
    );

Me right now =>