Hi,

I'm looking for a push in the right direction.

Using LLBLGEN 4.1, SQL Server, QuerySpec Adapter Queries.

I have (among quite a few other) the following tables in my project. Names can be somewhat confusing as they're from another system and not defined in our project: ProductTerm PK Fields: VocabularyId, TermId, ProductId Other fields: none

Vocabulary PK fields: VocabularyId, TermId Other field: ProductId

VocabularyLocale PK Fields: VocabularyId, Language Other Field: Name

Product PK Field: ProductId Other fields: several, but not relevant here

ProductLocale PK Fields: ProductId, Language Other field: Name

Now, I'm trying to write a query that gives me all Vocabulary records of products that are linked to a Term with TermId = 123 and VocabularyId = 'abc'. In SQL I'd write something like this

SELECT Vocabulary.VocabularyId, VocabularyLocale.Name
FROM ProductTerm
INNER JOIN Vocabulary ON <keys>
WHERE ProductTerm.ProductId IN 
(SELECT ProductId FROM ProductTerm WHERE TermId = 123 and VocabularyId = 'abc')

I'm sorry I didn't post real code. I could (and will if anyone wants), but I'm afraid it would make things way more complicated (for example, I also need to look for search words in the ProductLocale and TermLocale tables, for example table. And it's the concept of this WHERE ... IN (SELECT ... ) that I'm after.

In a more common Customers/Orders/Products scenario, I think a question like this would be pretty much the same: Give me al Orders of Customers that have Product X on at least one of their Orders.

I could really use a push in the right direction as to write a query like this.

Thanks!

Stefan

Now, I'm trying to write a query that gives me all Vocabulary records of products that are linked to a Term with TermId = 123 and VocabularyId = 'abc'. In SQL I'd write something like this

Code:

SELECT Vocabulary.VocabularyId, VocabularyLocale.Name FROM ProductTerm INNER JOIN Vocabulary ON <keys> WHERE ProductTerm.ProductId IN (SELECT ProductId FROM ProductTerm WHERE TermId = 123 and VocabularyId = 'abc')

Based on your requirement, I think the query should select from VocabularyLocale joined by Vocabulary.

I'm not sure why product and productTerm are being used. The Vocabulary already has termId & VocabularyId, correct?

Walaa wrote:

Based on your requirement, I think the query should select from VocabularyLocale joined by Vocabulary.

I'm not sure why product and productTerm are being used. The Vocabulary already has termId & VocabularyId, correct?

Thanks for your answer. ProductTerm is being used because that's where a feature (VocabularyId+TermId) are being linked to a product.

Basicly I need "All Vocabularies of all products that have a feature with TermId = 123 and VocabularyId = 'abc' linked to it.

If I just join ProductTerm to Vocabulary and VocabularyLocale, and filter for TermId = 123 and VocabularyId = 'abc', then I only get that one Vocabulary.

Any ideas?

And it's the concept of this WHERE ... IN (SELECT ... ) that I'm after.

In the example queries (installed with the installer in Frameworks\LLBLGen Pro\ExampleQueries\QuerySpec), see AdapterTests\EntityFetches.

[Test]
public void GetAllCustomersBasedOnCombinedQuery()
{
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        //LinqMetaData metaData = new LinqMetaData(adapter);
        //var q2 = (from c in metaData.Customer
        //        select c.Country).Distinct();
        //var q = from c in metaData.Customer
        //      where q2.Contains(c.Country)
        //      select c;
        //List<CustomerEntity> results = q.ToList();

        var qf = new QueryFactory();
        // distinct is defined in the query, but as it's converted to a field compare set predicate, the distinct isn't necessary: an IN(query) predicate
        // doesn't need a distinct in the query so it's filtered out.
        var q2 = qf.Customer.Select(CustomerFields.Country).Distinct();
        var q = qf.Customer
                        .Where(CustomerFields.Country.In(q2));
        var results = adapter.FetchQuery(q);
        Assert.AreEqual(90, results.Count);
    }
}

Here I use a separate query in a variable, but I can also inline it (so define the query q2 inside the In() operator). The q2 query is merged with the q query and executed as 1 query. Does this help?

Also look into .Any(), see GetCustomersWithEmployeesInGivenTerritoryUsingNestedAny in that same file. .Any() constructs a WHERE EXISTS() query which sounds like it might match your initial requirement as well.

The example queries contain parts of our unit tests, and should give you an idea how to use the various constructs.

Otis wrote:

Here I use a separate query in a variable, but I can also inline it (so define the query q2 inside the In() operator). The q2 query is merged with the q query and executed as 1 query. Does this help?

Also look into .Any(), see GetCustomersWithEmployeesInGivenTerritoryUsingNestedAny in that same file. .Any() constructs a WHERE EXISTS() query which sounds like it might match your initial requirement as well.

The example queries contain parts of our unit tests, and should give you an idea how to use the various constructs.

Sorry for my late reply, I don't seem to have notifications enabled for that thread or something.

That looks helpful indeed, thanks, I'll have a go at it that way.

Thanks, Stefan

Otis wrote:

And it's the concept of this WHERE ... IN (SELECT ... ) that I'm after.

[Test]
public void GetAllCustomersBasedOnCombinedQuery()
{
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        var qf = new QueryFactory();
        // distinct is defined in the query, but as it's converted to a field compare set predicate, the distinct isn't necessary: an IN(query) predicate
        // doesn't need a distinct in the query so it's filtered out.
        var q2 = qf.Customer.Select(CustomerFields.Country).Distinct();
        var q = qf.Customer
                        .Where(CustomerFields.Country.In(q2));
        var results = adapter.FetchQuery(q);
        Assert.AreEqual(90, results.Count);
    }
}

Here I use a separate query in a variable, but I can also inline it (so define the query q2 inside the In() operator). The q2 query is merged with the q query and executed as 1 query. Does this help?

Also look into .Any(), see GetCustomersWithEmployeesInGivenTerritoryUsingNestedAny in that same file. .Any() constructs a WHERE EXISTS() query which sounds like it might match your initial requirement as well.

The example queries contain parts of our unit tests, and should give you an idea how to use the various constructs.

Just to give a bit of positive feedback: above concept works like a blast for me. Feels like I'm getting closer to where i need to be in working with LLBLGen

Thanks!