Hello, I am working on putting together a query that checks a bunch of Lookup tables for 'active' records (indicated by an isActive field). The trick is that I would like run this in a single query just to avoid running multiple commands.

I suppose this could be done in two ways:

SELECT (SELECT IsActive FROM X WHERE Id = 1) IsXActive, (SELECT IsActive FROM Y WHERE Id =  2) IsYActive

or

SELECT 'X' fieldName, IsActive FROM X WHERE Id = '1'
UNION
SELECT 'Y' fieldName, IsActive FROM Y WHERE Id = '2'

While I prefer the first way, which gets a single row, I'd imagine LLBLGen might not have a way to create sql that doesn't have a FROM.

So I'm still learning QuerySpec (coming from the low-level api) and this is the basic query I started with to get the first select for X and I am struggling to get it right:

            var qf = new QueryFactory();
            var alias = "X";
            var q = qf.Create().Select(XFields.IsActive.Source(alias))
                .From(qf.X.As(alias))
                .Where(XFields.Id.Source(alias).Equal("1"));

            var adapter = new DataAccessAdapter();
            var results = adapter.FetchQuery(q);

And it generates this:

SELECT [LPA_r1].[IsActive] 
FROM (
   SELECT [dbo].[X].[Id], [dbo].[X].[IsActive], [dbo].[X].[Name], [dbo].[X].[UpdateBy], [dbo].[X].[UpdateDate], [dbo].[X].[Value] 
   FROM [dbo].[X]) [LPA_r1] 
WHERE ( ( [LPA_r1].[Id] ='1'))

This seems messy. Is there not a way to just create a query like:

SELECT 'IsXActive' fieldName, IsActive FROM X WHERE Id = '1'

... where it just selects the one field from the table you specify?

Notice, I still need to add the constant field name to my query, but I started simple and already running into trouble.

Once I figure out how to get the appropriate query produced, I will need to create many more like it and union them together to get a list of all the Lookups that I would like to validate and make sure they are active.

I'm sorry to bother with this, but I've tried many things and I get a lot of errors that I can't seem to resolve. So far, the C# example above is the only way I could do it without it blowing up.

Thanks for any info you can provide.

LLBLGEN 5.9.1, Runtime 5.9.0.0, Adapter, netcoreapp3.1, Azure SQL Database, No inheritance hierarchies used, no custom templates. Table Names are obfuscated, but the queries are exact.

I will need to create many more like it and union them together to get a list of all the Lookups that I would like to validate and make sure they are active.

• Do you want to fetch IsActive lookups? shouldn't this return multiple records using a filter on IsActive?
• Usually, I'd recommend lookups to be loaded in memory, if they are not holding too many records. Pre-loading isActive lookups for readOnly usages can save you a lot at dev time, and at run time.

Before jumping into the solution, please allow me to discuss the approach. Maybe this can be dealt with in a different way.

I'm currently working on the right caching solution. It is a distributed system, so in-memory isn't really an option. Redis Cache is an obvious consideration, but we're looking into cosmosDb as well because we have some other use cases for it and it performs well for key value lookups.

But for now, I'd really like to learn how to write a query like this...

SELECT 'IsXActive' fieldName, IsActive FROM X WHERE Id = '1'
UNION
SELECT 'IsYActive' fieldName, isActive FROM Y WHERE Id = '2'

Let me know if you can help. I've been struggling wrapping my head around the way the QuerySpec works to get it to behave the way I would expect.

usschad wrote:

I'm currently working on the right caching solution. It is a distributed system, so in-memory isn't really an option. Redis Cache is an obvious consideration, but we're looking into cosmosDb as well because we have some other use cases for it and it performs well for key value lookups.

But for now, I'd really like to learn how to write a query like this...

SELECT 'IsXActive' fieldName, IsActive FROM X WHERE Id = '1'
UNION
SELECT 'IsYActive' fieldName, isActive FROM Y WHERE Id = '2'

Let me know if you can help. I've been struggling wrapping my head around the way the QuerySpec works to get it to behave the way I would expect.

I've written the queries you wanted below.

using(var adapter = new DataAccessAdapter())
{
    var qf = new QueryFactory();
    var q = qf.Order
              .Select(() => new
                            {
                                C1=qf.Order.Where(OrderFields.OrderId.Equal(10254)).Select(OrderFields.CustomerId).ToScalar().As("CustomerId1").ToValue<string>(),
                                C2=qf.Customer.Where(CustomerFields.CustomerId.Equal("CHOPS")).Select(CustomerFields.Country).ToScalar().As("Country").ToValue<string>(),
                            });
    var results = adapter.FetchQuery(q);
    Assert.AreEqual("CHOPS", results[0].C1);
    Assert.AreEqual("Switserland", results[0].C2);
}

gives:

SELECT (SELECT [Northwind].[dbo].[Orders].[CustomerID] AS [CustomerId]
        FROM   [Northwind].[dbo].[Orders]
        WHERE  (([Northwind].[dbo].[Orders].[OrderID] = @p1)))       AS [CustomerId1],
       (SELECT [Northwind].[dbo].[Customers].[Country]
        FROM   [Northwind].[dbo].[Customers]
        WHERE  (([Northwind].[dbo].[Customers].[CustomerID] = @p2))) AS [Country]
FROM   [Northwind].[dbo].[Orders] 

It'll use the from target of the first scalar, but that's not important. Keep in mind that this isn't very fast

The queries needed for the union query can be done like this:

using(var adapter = new DataAccessAdapter())
{
    var qf = new QueryFactory();
    var q = qf.Order
              .Where(OrderFields.OrderId.Equal(10254))
              .Select(()=> new {FieldName="Foo", Foo=OrderFields.CustomerId.As("Foo").ToValue<string>()});
    var results = adapter.FetchQuery(q);
    foreach(var r in results)
    {
        Console.WriteLine("Fieldname: {0}, Foo: {1}", r.FieldName, r.Foo);
    }
}

gives:

SELECT [Northwind].[dbo].[Orders].[CustomerID] AS [Foo]
FROM   [Northwind].[dbo].[Orders]
WHERE  (([Northwind].[dbo].[Orders].[OrderID] = @p1)) 

which results in: Fieldname: Foo, Foo: CHOPS as output . As you can see they're flat and don't wrap a derived table. There's no aliasing needed as you did, so no wrapping.

The idea is that the constant you pass in is placed in the object returned in the projection, and not send to the database. Above I've written one query but you can union queries together using the .Union() method, see: https://www.llblgen.com/Documentation/5.9/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/QuerySpec/gencode_queryspec_generalusage.htm#merging-resultsets-union-and-unionall So it comes down to something like this:

var qf = new QueryFactory();
var q1 = qf.X
          .Where(XFields.Id.Equal(10254))
          .Select(()=> new {FieldName="XActive", IsActive=XFields.IsActive.ToValue<bool>()});
var q2 = qf.Y
          .Where(YFields.Id.Equal(10254))
          .Select(()=> new {FieldName="YActive", IsActive=YFields.IsActive.ToValue<bool>()});
var q = q1.Union(q2);
var results = adapter.FetchQuery(q);

The type I specified above is bool in the .ToValue<T>() method call, which is essential to obtain the value from the resultset. I used an anonymous type here, but you likely want to specify a small class for the resulttype to project the data into.