Hi,

I need to know if this query could be translated into a single hit self service query using a collection

SELECT DISTINCT [UnionAll1].[LocalizedItemID] AS [LocalizedItemID], [UnionAll1].[LocalizedTargetId] AS [LocalizedTargetID], [UnionAll1].[LocalizedTargetTypeID] AS [LocalizedTargetTypeID] INTO #LocalizedShortcutTable FROM ( SELECT [Shortcut].[LocalizedItemID] AS [LocalizedItemID], [Shortcut].[LocalizedTargetId] AS [LocalizedTargetId], [Shortcut].[LocalizedTargetTypeID] AS [LocalizedTargetTypeID], [Shortcut].[LocalizedTargetId] AS [LocalizedTargetId1] FROM [dbo].[ContentItems_LocalizedShortcut] AS [Shortcut] WHERE [Shortcut].[LocalizedItemID] IN ( SELECT [n] FROM @PublishedItemMetadataIds) UNION ALL SELECT [Join2].[LocalizedItemID1] AS [LocalizedItemID], [Join2].[LocalizedTargetId] AS [LocalizedTargetId], [Join2].[LocalizedTargetTypeID] AS [LocalizedTargetTypeID], [Join2].[LocalizedTargetId] AS [LocalizedTargetId1] FROM [dbo].[ContentItems_Published] AS [PublishedItem] INNER JOIN ( SELECT [ContentItems_LocalizedShortcut].[LocalizedItemID] AS [LocalizedItemID1], [ContentItems_LocalizedShortcut].[LocalizedTargetId] AS [LocalizedTargetId], [ContentItems_LocalizedShortcut].[LocalizedTargetTypeID] AS [LocalizedTargetTypeID], [Join1].[ContentItems_LocalizedItem_LocalizedItemID], [Join1].[ContentItems_Published_LocalizedItemID] FROM [dbo].[ContentItems_LocalizedShortcut] AS [ContentItems_LocalizedShortcut] INNER JOIN (

            SELECT 
                [ContentItems_LocalizedItem].[LocalizedItemID] AS [ContentItems_LocalizedItem_LocalizedItemID], 
                [ContentItems_Published].[LocalizedItemID] AS [ContentItems_Published_LocalizedItemID] 
            FROM  [dbo].[ContentItems_LocalizedItem] AS [ContentItems_LocalizedItem] 
            INNER JOIN [dbo].[ContentItems_Published] AS [ContentItems_Published] 
            ON [ContentItems_LocalizedItem].[LocalizedItemID] = [ContentItems_Published].[LocalizedItemID] ) AS [Join1] 
            ON [Join1].[ContentItems_LocalizedItem_LocalizedItemID] = [ContentItems_LocalizedShortcut].[LocalizedTargetId] ) AS [Join2] 
            ON [PublishedItem].[LocalizedItemID] = [Join2].[ContentItems_Published_LocalizedItemID] 


            WHERE ([PublishedItem].[LocalizedContentItemId] IN (
                SELECT [n] FROM @PublishedItemMetadataIds)) 
                    OR ([PublishedItem].[LocalizedItemID] IN (SELECT [n] FROM @PublishedItemMetadataIds))) 
                    AS [UnionAll1] 

SELECT [LocalizedItem].[LocalizedItemID] AS [LocalizedItemId], [LocalizedItem].[LocalizedItemID] AS [LocalizedTargetId], [LocalizedItem].[LanguageID] AS [LanguageId], [LocalizedItem].[Name] AS [Name], [LocalizedItem].[PublishState] AS [PublishState], [LocalizedItem].[LocalizedType] AS [PublishedItemTypeId], [PublishedItem].[SourceTypeID] AS [SourceTypeId] FROM [dbo].[ContentItems_LocalizedItem] AS [LocalizedItem] LEFT OUTER JOIN [dbo].[ContentItems_Published] AS [PublishedItem] ON [LocalizedItem].[LocalizedItemID] = [PublishedItem].[LocalizedItemID] WHERE [LocalizedItem].[LocalizedItemID] IN (SELECT [LocalizedTargetID] FROM #LocalizedShortcutTable)

DROP TABLE #LocalizedShortcutTable END

I do realize its a mess at it stands, but I need this as a POC under load to decide whether to use LLBLGen as our ORM or not. thus far it has held up to the load, however we need to ensure it will under this sort of query.

What does it do, semantically? (what is it that you want to fetch/obtain) ? Our ORM doesn't support UNION, however in many cases there's a workaround for what is to be accomplished.

Hi,

It is a query inside a query inside a query, essentially the inner's results drive the results to an outer query, which is stored in a temp table to derive some results from.

This is because the results of one drives the seconds, and we couldn't get the third directly which is why we have created a temp table (for getting our minds around, and speed)

Essentially can we pas a query (derived from selfservice) as a parameter of another query (derived from self service) with the potential for joins between?

It is a query inside a query inside a query, essentially the inner's results drive the results to an outer query, which is stored in a temp table to derive some results from.

This is because the results of one drives the seconds, and we couldn't get the third directly which is why we have created a temp table (for getting our minds around, and speed)

Essentially can we pas a query (derived from selfservice) as a parameter of another query (derived from self service) with the potential for joins between?

Still I don't get the the need for the Union.

Could you please simplify the query needed in the simplest possible form (pseudocode)?

The query itself doesnt matter, its an example of the concept we are trying to get from it, does LLBLGenhave the ability to pass generated queries through as a result of a filter of another

ie

select 1 from table1 where item1 in (select item2 from table2 where item3 in (select item]4 from table 3))

can this kind of query be translated into a selfservice query with a single hit on the databse?

DevTeamSA wrote:

The query itself doesnt matter, its an example of the concept we are trying to get from it, does LLBLGenhave the ability to pass generated queries through as a result of a filter of another

ie

select 1 from table1 where item1 in (select item2 from table2 where item3 in (select item]4 from table 3))

can this kind of query be translated into a selfservice query with a single hit on the databse?

yes, it can deal with correlated subqueries and derived tables (queries used as sources for projections in FROM clauses) and can do what you require. E.g.:

// queryspec:
var qf = new QueryFactory();
var q = qf.Create()
                .Select(() => new
                {
                    City = CustomerFields.City.Source("a").ToValue<string>(),
                    ContactName = CustomerFields.ContactName.Source("a").ToValue<string>()
                })
                .From(qf.Customer
                        .From(QueryTarget.InnerJoin(CustomerEntity.Relations.OrderEntityUsingCustomerId))
                        .OrderBy(CustomerFields.City.Ascending())
                        .Where(CustomerFields.Country == "UK")
                        .AndWhere(CustomerFields.Phone != "555-5555")
                        .Select(CustomerFields.City, CustomerFields.ContactName)    
                        .As("a"))
                .Where(CustomerFields.City.Source("a") == "London");
var results = new TypedListDAO().FetchQuery(q);

// linq
LinqMetaData metaData = new LinqMetaData();
var q = from c in metaData.Customer
        join o in metaData.Order on c.CustomerId equals o.CustomerId
        let m = c.Phone
        orderby c.City
        where c.Country == "UK"
        where m != "555-5555"
        select new { c.City, c.ContactName } into a
        where a.City == "London"
        select a;

// SQL output
SELECT [LPA_a1].[City],
       [LPA_a1].[ContactName]
FROM   (SELECT [Northwind].[dbo].[Customers].[City],
               [Northwind].[dbo].[Customers].[ContactName]
        FROM   ( [Northwind].[dbo].[Customers]
                 INNER JOIN [Northwind].[dbo].[Orders]
                     ON [Northwind].[dbo].[Customers].[CustomerID] = [Northwind].[dbo].[Orders].[CustomerID])
        WHERE  (([Northwind].[dbo].[Customers].[Country] = @p1
             AND [Northwind].[dbo].[Customers].[Phone] <> @p2))) [LPA_a1]
WHERE  (([LPA_a1].[City] = @p3)) 

or

// queryspec
var qf = new QueryFactory();
var q = qf.Create()
            .Select(CustomerFields.Country.Source("g"), qf.Field("g", "Num"))
            .From(qf.Create()
                        .Select(CustomerFields.Country.Source("c"),
                                qf.Field("c", "NumberOfODRows").Sum().As("TotalNumberOfODRows"),
                                OrderFields.EmployeeId.Source("c").Avg().As("Num"))
                        .From(qf.Order
                                    .From(QueryTarget.InnerJoin(OrderEntity.Relations.CustomerEntityUsingCustomerId))
                                    .Select(CustomerFields.Country,
                                            OrderFields.EmployeeId,
                                            qf.OrderDetail
                                                    .CorrelatedOver(OrderEntity.Relations.OrderDetailEntityUsingOrderId)
                                                    .CountRow().As("NumberOfODRows"))
                                    .As("c"))
                        .GroupBy(CustomerFields.Country.Source("c"))
                        .Having(qf.Field("c", "NumberOfODRows").Sum().GreaterThan(10))
                        .As("g"))
            .OrderBy(CustomerFields.Country.Source("g").Ascending());
var results = new TypedListDAO().FetchQuery(q);

// linq, not entirely the same as Linq doesn't support Having clauses
LinqMetaData metaData = new LinqMetaData();
var q = from o in metaData.Order
        group o by o.Customer.Country into g
        orderby g.Key
        where g.Sum(n => n.OrderDetails.Count()) > 10
        select new { Country = g.Key, Num = g.Average(n => n.EmployeeId) };

// SQL
SELECT [LPA_g1].[Country],
       [LPA_g1].[Num]
FROM   (SELECT [LPA_c2].[Country],
               SUM([LPA_c2].[NumberOfODRows]) AS [TotalNumberOfODRows],
               AVG([LPA_c2].[EmployeeId])    AS [Num]
        FROM   (SELECT [Northwind].[dbo].[Customers].[Country],
                       [Northwind].[dbo].[Orders].[EmployeeID]
                       AS
                               [EmployeeId],
                       (SELECT COUNT(*) AS [LPAV_]
                        FROM   [Northwind].[dbo].[Order Details]
                        WHERE  ((([Northwind].[dbo].[Order Details].[OrderID] = [Northwind].[dbo].[Orders].[OrderID]))))
                       AS
                               [NumberOfODRows]
                FROM   ( [Northwind].[dbo].[Customers]
                         INNER JOIN [Northwind].[dbo].[Orders]
                             ON [Northwind].[dbo].[Customers].[CustomerID] = [Northwind].[dbo].[Orders].[CustomerID]))
               [LPA_c2]
        GROUP  BY [LPA_c2].[Country]
        HAVING (SUM([LPA_c2].[NumberOfODRows]) > @p2)) [LPA_g1]
ORDER  BY [LPA_g1].[Country] ASC 

Thanks for the feedback, I was hoping to get a direct call through self service without going through queryspec or linq

DevTeamSA wrote:

Thanks for the feedback, I was hoping to get a direct call through self service without going through queryspec or linq

you can, with FieldCompareSetPredicate, DynamicRelation and DerivedTable instances, but it will be more code, and you will not be able to e.g. use resultset caching and async fetches. I don't really see why you would want to use the low-level api instead of queryspec or linq. (they convert to the low-level API elements).