Best way to query a database using an OR statement

Question

I am attemping to create the following sql script into a subsonic query that I can use with a collection:

Select * from tableA
where tableA.InvoiceID = @Invoice
and tableA.VersionID = @VersionID
and tableA.ActiveDate >= GetDate()
and (tableB.InActiveDate is null or tableB.InActiveDate <= GetDate())

Here is my current code:

orders = new OrdersCollection();
query = new SubSonic.Query(Tables.Orders);
query.WHERE("InvoiceID", Invoice.InvoiceID);
query.AND("VersionID", version.VersionID);
query.AND("ActiveDate", SubSonic.Is.LessThanOrEqualTo(System.DateTime.Now.ToString()).Value);
query.AND("InActiveDate", SubSonic.Comparision.Is, null).OR("InActiveDate", SubSonic.Is.GreaterThanOrEqualTo("System.DateTime.Now.ToString()).Value);

query.CommandTimeOut = intSubSonicTimeOut; partXrefColl.Load(FilePartXref.FetchByQuery(query));

The problem that I think I am running into is that the evaulation of the dates and the OR statement. I have tried it with just the InvoiceID and VersionID and I get data back to the collection.

Any help will be greatly appreciated.

Answer 1

You SQL shows a "less than or equal to" operator (<=), but you code says GreaterThanOrEqualTo(). Could the reversal of the logic cause your problem?

Answer 2

Sorry the correct syntax for the sql query should have been:

Select * from tableA
where tableA.InvoiceID = @Invoice
and tableA.VersionID = @VersionID
and tableA.ActiveDate <= GetDate()
and (tableB.InActiveDate is null or tableB.InActiveDate >= GetDate())

And the Code should have been:

orders = new OrdersCollection();
query = new SubSonic.Query(Tables.Orders);
query.WHERE("InvoiceID", Invoice.InvoiceID);
query.AND("VersionID", version.VersionID);
query.AND("ActiveDate", SubSonic.Is.LessThanOrEqualTo(System.DateTime.Now.ToString()).Value);
query.AND("InActiveDate", SubSonic.Comparision.Is, null).OR("InActiveDate", SubSonic.Is.GreaterThanOrEqualTo("System.DateTime.Now.ToString()).Value);

orders.Load(Invoices.FetchByQuery(query);

Sorry for the confusion.

Answer 3

Wild guess:

"SubSonic.Comparision.Is, null" is not mapping to "IS NULL", instead it's mapping to "= NULL"

Answer 4

Assuming you're using SubSonic 2.1 or above you can write that query as:

OrdersCollection orders = DB.Select.From(Orders.Schema)
  .Where(Orders.Columns.InvoiceID).IsEqualTo(1)
  .And(Orders.Columns.VersionID).IsEqualTo(1)
  .And(Orders.Columns.ActiveDate).IsLessThanOrEqualTo(DateTime.Now)
  .AndExpression(Orders.Columns.InActiveDate).IsNull()
  .Or(Orders.Columns.InActiveDate).IsGreaterThanOrEqualTo(DateTime.Now)
  .ExecuteAsCollection<OrdersCollection>();

The AndExpression will cause the Or constraint after it to be nested

Answer 5

Adam has it exactly - the thing you're missing is the Expression part of it which is given with AndExpression

Answer 6

I don't clearly understand what you are talking about. Can you please use the existing example as a basis to example your thoughts.

Answer 7

I used the sample by Adam above but I am getting the following errors.

Error 2 A local variable named 'OrdersColl' cannot be declared in this scope because it would give a different meaning to 'OrdersColl', which is already used in a 'parent or current' scope to denote something else

Error 3 An object reference is required for the nonstatic field, method, or property 'SubSonic.SqlQuery.From(SubSonic.TableSchema.Table)'

Error 4 The name 'IsGreaterThanOrEqualTo' does not exist in the current context

Below is my code that is generating the error:

OrdersCollection OrdersColl = SubSonic.Select.From(Order.Schema)
                    .Where(Order.Columns.VersionID).IsEqualTo(version.VersionID)
                    .And(Order.Columns.InvoiceID).IsEqualTo(Invoice.InvoiceID)
                    .And(Order.Columns.ActiveDate).IsLessThanOrEqualTo(DateTime.Now)
                    .AndExpression(Order.Columns.InactiveDate).IsNull()
                    .Or("InActiveDate",IsGreaterThanOrEqualTo(DateTime.Now))
                    .ExectueAsCollection<OrdersCollection>();

Answer 8

I need to populate the results into the existing OrdersColl since I use it to cache the collection with my project for later use in the service. In the past I used the query method and set it to the collection via a load using the Collection.Load(Order.FetchByQuery(query)). Is this still not a vaiable option ?

The type or namespace name 'DB' does not exist in the namespace 'SubSonic' (are you missing an assembly reference?)

As for the DB.Select I am not seeing that as an option with the intellisense.

Answer 9

After a little debugging I was able to complete the query with the following:

 OrderColl =  DB.Select().From(Orders.Schema)
                    .Where(Orders.Columns.VersionID).IsEqualTo(version.VersionID)
                    .And(Orders.Columns.FulfillNumber).IsEqualTo(invoice.InvocieID)
                    .And(Orders.Columns.ActiveDate).IsLessThanOrEqualTo(DateTime.Now)
                    .AndExpression(Orders.Columns.InactiveDate).IsNull()
                    .Or("InActiveDate").IsGreaterThanOrEqualTo(DateTime.Now)
                    .ExecuteAsCollection<OrdersCollection>();

Thanks to everyone for all of your assistance.

Answer 10

sorry wrong pst