Hi,

Complete llblgen rookie here I'm currently evaluating llblgen on a not-so-large customer project.

I use llblgen pro 5.5, mysql 5.5, asp.net website - fw 4.7.2

I have a documentversion entity (think documents with titles and other properties), a tag entity (think... tags... with a string value) and a TagsOnVersion table which combines these in an m:n relationship.

I can query for documentversions like this:

var documentVersions = new EntityCollection<DocumentversionEntity>();

using (var adapter = new DataAccessAdapter())
{
    var qf = new QueryFactory();

    var q = qf.Documentversion;

    // Text search (search in title)
    if (!string.IsNullOrWhiteSpace(uiTextSearchInput.Text))
    {
        q = q.AndWhere(DocumentversionFields.Title.Contains(uiTextSearchInput.Text) );
    }

    // Tags search
    //if (!string.IsNullOrWhiteSpace(uiTagsSearchInput.Text))
    //{
    //  var stringTags = uiTagsSearchInput.Text.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
    //  foreach (var stringTag in stringTags)
    //  {
    //      // Old Telerik code:
    //      //query = query.Where(dv => dv.Tags.Contains(tag));
    //  }
    //}

    q = q.WithPath(DocumentversionEntity.PrefetchPathTagCollectionViaTagsonversion);

    adapter.FetchQuery(q, documentVersions);
}

// binding documentVersions to grid here

I have included some of the old Telerik ORM code which would lazy-load the Tags collection and hence I was able to pick those documentversions which had a Tags list that contained one of the specified tags in the search box. Not a very way, but performance is not really a consideration here.

However, I have figured out how to load the tags with the PrefetchPath - but I'm unable to add a filter, so I get the existing behaviour.

I would imagine something like

q = q.AndWhere(DocumentversionFields.TagsCollectionViaTagsonversion.Contains(...))

but thats not possible.

Any help would be appreciated.

BR, Jan

To filter on a related entity you need to use joins.

Example:

var qf = new QueryFactory();
var query = qf.Order
     .From(QueryTarget.InnerJoin(qf.OrderDetail)
    .On(OrderFields.OrderId == OrderDetailFields.OrderId))
    .From(QueryTarget.InnerJoin(qf.Product)
    .On(ProductFields.ProductId == OrderDetailFields.ProductId)).Where(ProductFields.CategoryId == 2)
    .Select(OrderFields.OrderId);

var list = adapter.FetchQuery(query);

Thanks. I've added the many-to-many tables with joins now:

var documentVersions = new EntityCollection<DocumentversionEntity>();
var stringTags = new List<string>();

using (var adapter = new DataAccessAdapter())
{
    var qf = new QueryFactory();

    var q = qf.Documentversion
        .From(QueryTarget.InnerJoin(DocumentversionEntity.Relations.TagsonversionEntityUsingDocumentVersionId)
            .InnerJoin(TagsonversionEntity.Relations.TagEntityUsingTagId)
            );

    // Text search (search in title)
    if (!string.IsNullOrWhiteSpace(uiTextSearchInput.Text))
    {
        q = q.AndWhere(DocumentversionFields.Title.Contains(uiTextSearchInput.Text) );
    }

    // Tags search
    if (!string.IsNullOrWhiteSpace(uiTagsSearchInput.Text))
    {
        stringTags.AddRange(uiTagsSearchInput.Text.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries));
        foreach (var stringTag in stringTags)
        {
            // Old Telerik code:
            //query = query.Where(dv => dv.Tags.Contains(tag));
            
            // LLBLGen code here 1...
        }

        // LLBLGen code here 2...

        //q = q.AndWhere(TagFields.Value.In(stringTags));
        // this corresponds to SQL ...WHERE tag.value IN ('t1', 't2')
        // but returns all documentversions with tag t1 OR t2, which is too many
        // what we want, is all documentversions with tags t1 AND t2
    }

    q = q.WithPath(DocumentversionEntity.PrefetchPathTagCollectionViaTagsonversion);

    adapter.FetchQuery(q, documentVersions);
}

var documentVersionsFiltered = new EntityCollection<DocumentversionEntity>();

foreach (var documentversionEntity in documentVersions)
{

    var allTagsValues = documentversionEntity.TagCollectionViaTagsonversion.Select(t => t.Value);

    if (allTagsValues.Intersect(stringTags).Count() == stringTags.Count)
    {
        documentVersionsFiltered.Add(documentversionEntity);

    }
}

uiDocumentVersions.DataSource = documentVersionsFiltered;

And I'm now able to do some filtering on the tag values. However, it still doesn't quite behave as desired.

Consider this example:

document A has tags x and y document B has tags x and z document C has tags y and z

if I uncomment the line

//q = q.AndWhere(TagFields.Value.In(stringTags));

and search for x I get document A and B. If I search for x and y (stringTags list has two entries) I get document A, B and C, where I would like to get only A, but because the generated query is something like WHERE tag.value IN ('x', 'y') all three documentversions will be found.

Any advice on how to correctly construct a QuerySpec to overcome this?

I would imagine adding something for each tag where I've added // LLBLGen code here 1.... or something for the collection of tags being searched for where I've added // LLBLGen code here 2...

BR, Jan

The kind of filtering used, is not suitable for your case.

This is a SQL problem See these stack overflow posts: https://stackoverflow.com/questions/13889547/select-all-rows-that-have-at-least-a-list-of-features

https://stackoverflow.com/questions/17278695/sql-query-selecting-a-row-if-it-has-all-related-options-in-a-related-table

So the way to go is usually to use a groupby + a having clause which does a count on the grouped set so that the count is equal to the number of elements (tags in your case) in the IN clause. A parent row that matches a subset of the set of tags specified has less elements in the group so the count will be less than the value checked in the having clause.

With Queryspec you have to use a correlated subquery here which contains the joins.

I'll try to work out an example for you...

(Edit) Example based on Northwind...

Trying to find Orders, having all of the following products (ProductID: 44, 59 & 73).

var qf = new QueryFactory();

var query = qf.OrderDetail
    .Select(OrderDetailFields.OrderId, Functions.CountRow().As("NumberOfProductsFound"))
     .From(QueryTarget.InnerJoin(qf.Product)
    .On(ProductFields.ProductId == OrderDetailFields.ProductId))
    .Where(ProductFields.ProductId == 44).OrWhere(ProductFields.ProductId == 59).OrWhere(ProductFields.ProductId == 73)
    .GroupBy(OrderDetailFields.OrderId);

var query2 = qf.Create()
    .From(query.As("p"))
    .Where(qf.Field("p", "NumberOfProductsFound").Equal(3))
    .Select(() => qf.Order
        .CorrelatedOver(OrderFields.OrderId == OrderDetailFields.OrderId.Source("p"))
        .ToResultset()
    );

var list = adapter.FetchQuery(query2);