Hi,

Few months ago I was building a MetaFramework and I've asked you how to dynamicly nest queries (http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=14247).

public Test()
{
            var filtered = LastestDocuments();

            filtered = FilterCustomAny(filtered, ((MetaDataEntity m) => m.Name == "Name" && m.Text == "Maciek"));
            filtered = FilterCustomAll(filtered, ((MetaDataEntity m) => m.Name != "LT"));

            Assert.IsEqual(filtered.Count,1);
}


public IQueryable<MetaDocumentEntity> LastestDocuments()
{
            LinqMetaData metaData = new LinqMetaData();

            var ids = (from c in metaData.MetaDocument
                       select c.OriginalDocument.DescendantDocuments.Select(d => (long)d.ID).Max()).Distinct();

            var result = (from c in metaData.MetaDocument
                          where ids.Contains(c.ID)
                          select c);

            return result;
}

public IQueryable<MetaDocumentEntity> FilterCustomAny(IQueryable<MetaDocumentEntity> toFilter, Func<MetaDataEntity,bool> predicate)
{
            return toFilter.Where(d => d.MetaDatas.Any(predicate));
}
public IQueryable<MetaDocumentEntity> FilterCustomAll(IQueryable<MetaDocumentEntity> toFilter, Func<MetaDataEntity, bool> predicate)
{
            return toFilter.Where(d => d.MetaDatas.All(predicate));
}

Back then it was preaty simple. Metadocuments had metadatas and you filtered metadocuments based on what matadats they had.

Now I'd like it to be more like a tree. Metadatas doses not only belong to Metadocuments but alsow to other metadatas. This change would require quite a lot of work (adjusting existing applications ect.) so I'd like to ask you if something like this is possible (and if so how slow it would be) before I start:

public Test()
{
            var filtered = LastestDocuments();

filtered = FilterCustomAny(filtered, ((MetaDataEntity m) => m.Name == "DualDate" && m.MetaDatas.Any((n) => n.Text == "Start" && n.Date == "11.11.2011")));

filtered = FilterCustomAny(filtered, ((MetaDataEntity m) => m.Name == "DualDate" && m.MetaDatas.Any((n) => n.Text == "End" && n.Date == "12.11.2011")));

            Assert.IsEqual(filtered.Count,1);
}

That is: I want all documents that have metadata named "DualDate" and this metadata has metadata named "Start" with value "11.11.2011" and "End" with value "12.11.2011".

So .. how slow would it work (if at all)?

Thanks in advance

Have you tried that? What is the generated sql for that last approach?

Actually .. no, I have not

There was a bug in the LLBLpro version I was working on so i just took Otis word that it would work and focused on coding other parts of the project. Now that almost everything else is finished i'm back to this problem. Guess, i'll have to try it to find out

We'll be waiting for your feedback.

I finally found some time to test it. Well ... lucky me it dosn't work as expected

(This is still about the simpler approach. Documents have metadatas but metadatas dosn't have other metadatas)

Here are some detiles:

            metaData = new LinqMetaData();

            documents = from d in metaData.MetaDocument select d;
            Assert.AreEqual(documents.Count(), 3);

            var filtered1 = documents.Where(d1 => d1.MetaDatas.Any((m1) => m1.Name == "Other" && m1.Boolean == false));
            Assert.AreEqual(filtered1.Count(), 2);

            var filtered2 = documents.Where(d2 => d2.MetaDatas.Any((m2) => m2.Name == "Metadata"));
            Print(filtered2, 2);
            Assert.AreEqual(filtered2.Count(), 3);

            var filtered12 = filtered1.Where(d3 => d3.MetaDatas.Any((m3) => m3.Name == "Metadata"));
            Print(filtered12, 12);
            Assert.AreEqual(filtered12.Count(), 2); // Error [0]!=[2];

filtered1 are those documents that have metadata named "Other" and with Boolean field equal "False". filtered2 are those documents that have metadata named "Methadata". filtered12 are all those that ware in filtered1 except those that doesn't have metadata named "Methadata" (1 and 2 combined).

And here's the generated query:

The thread 0xf00 has exited with code 0 (0x0).
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [LPLA_1].[ID], [LPLA_1].[RootID], [LPLA_1].[Type] FROM [BAW].[dbo].[MetaDocument] [LPLA_1] 

Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [LPLA_3].[GUID] FROM [BAW].[dbo].[MetaData] [LPLA_3]  WHERE ( [LPLA_3].[ID] = [LPA_M2].[MetadataID] AND ( ( [LPLA_3].[Name] = @Name2) AND ( [LPLA_3].[Boolean] = @Boolean3)))
    Parameter: @Name2 : String. Length: 32. Precision: 0. Scale: 0. Direction: Input. Value: "Other".
    Parameter: @Boolean3 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: False.

Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [LPLA_3].[GUID] FROM [BAW].[dbo].[MetaData] [LPLA_3]  WHERE ( [LPLA_3].[ID] = [LPA_M2].[MetadataID] AND ( [LPLA_3].[Name] = @Name4))
    Parameter: @Name4 : String. Length: 32. Precision: 0. Scale: 0. Direction: Input. Value: "Metadata".

Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Generated Sql query: 
    Query: SELECT DISTINCT TOP(@top0) COUNT(*) AS [LPAV_] FROM ((SELECT [LPLA_1].[ID], [LPLA_1].[RootID], [LPLA_1].[Type] FROM [BAW].[dbo].[MetaDocument] [LPLA_1] ) [LPA_L1] INNER JOIN [BAW].[dbo].[MetaDataInMetaDocument] [LPA_M2]  ON  [LPA_L1].[ID]=[LPA_M2].[DocumentID]) WHERE ( ( (  EXISTS (SELECT [LPLA_3].[GUID] FROM [BAW].[dbo].[MetaData] [LPLA_3]  WHERE ( [LPLA_3].[ID] = [LPA_M2].[MetadataID] AND ( ( [LPLA_3].[Name] = @Name2) AND ( [LPLA_3].[Boolean] = @Boolean3))))) AND  EXISTS (SELECT [LPLA_3].[GUID] FROM [BAW].[dbo].[MetaData] [LPLA_3]  WHERE ( [LPLA_3].[ID] = [LPA_M2].[MetadataID] AND ( [LPLA_3].[Name] = @Name4)))))
    Parameter: @top0 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
    Parameter: @Name2 : String. Length: 32. Precision: 0. Scale: 0. Direction: Input. Value: "Other".
    Parameter: @Boolean3 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: False.
    Parameter: @Name4 : String. Length: 32. Precision: 0. Scale: 0. Direction: Input. Value: "Metadata".

            metaData = new LinqMetaData();

            documents = from d in metaData.MetaDocument select d;
            Assert.AreEqual(documents.Count(), 3);

            var filtered1 = documents.Where(d1 => d1.MetaDatas.Any((m1) => m1.Name == "Other" && m1.Boolean == false));
            Assert.AreEqual(filtered1.Count(), 2);

            var filtered2 = documents.Where(d2 => d2.MetaDatas.Any((m2) => m2.Name == "Metadata"));
            Print(filtered2, 2);
            Assert.AreEqual(filtered2.Count(), 3);

            var filtered12 = filtered1.Where(d3 => d3.MetaDatas.Any((m3) => m3.Name == "Metadata"));
            Print(filtered12, 12);
            Assert.AreEqual(filtered12.Count(), 2); // Error [0]!=[2];

Why do you expect the last resultset to have a count of 2?

Would you post some sample data from the database tables and which SQL queries do you expect to be run and what resultset do you expect as a result of running these queries upon the provided data set.

I don't have access to the DB right now so i can't give you exact row by row data.

Test's start like this:

            DocumentEntity document = new DocumentEntity(DocumentType.User);
            document.AddMetadata(new MetadataEntity("Metadata"));
            document.AddMetadata(new MetadataEntity("Date", DateTime.Now.AddDays(-2.0)));
            document.AddMetadata(new MetadataEntity("Version", 1));
            document.AddMetadata(new MetadataEntity("Other", true));
            document.CompleteSave();

            DocumentEntity newDocument = document.NewVersion(false);
            newDocument["Version"].Integer = 2;
            newDocument["Date"].Date = DateTime.Now.AddDays(-1.0);
            newDocument["Other"].Boolean = false;
            newDocument.CompleteSave();

            DocumentEntity evenNewerDocument = newDocument.NewVersion(false);
            evenNewerDocument["Version"].Integer = 3;
            evenNewerDocument["Date"].Date = DateTime.Now;
            evenNewerDocument.CompleteSave();

There are 3 documents in the DB. Each version of the document inherits metadatas from previous version. So every metadata added to 'document' is present in 'newDocument' and 'evenNewerDocument'. Metadatas can be overrided (like 'Version', 'Date' and 'Other').

1) Every document has metadata named "Metadata" 2) Two (last two) documents has metadata named "Other" with value "False"

What I would like to get? Something like this (but faster):

Assert.AreEqual(filtered1.Intersect(filtered2),2); // Works fine

If raw DB data and sample queries are needed i'll post as soon as i get access to DB and current MetaFramework code. Thanks

Before you start posting things, I'd like to know what is it that you've trouble with? Because it seems like you're asking for something if it will work, but it's unclear what exactly and what you've tried yourself and (!) what problems that gave you.

Query: SELECT DISTINCT TOP(@top0) COUNT(*) AS [LPAV_] FROM ((SELECT [LPLA_1].[ID], [LPLA_1].[RootID], [LPLA_1].[Type] FROM [BAW].[dbo].[MetaDocument] [LPLA_1] ) [LPA_L1] INNER JOIN [BAW].[dbo].[MetaDataInMetaDocument] [LPA_M2] ON [LPA_L1].[ID]=[LPA_M2].[DocumentID]) WHERE ( ( ( EXISTS (SELECT [LPLA_3].[GUID] FROM [BAW].[dbo].[MetaData] [LPLA_3] WHERE ( [LPLA_3].[ID] = [LPA_M2].[MetadataID] AND ( ( [LPLA_3].[Name] = @Name2) AND ( [LPLA_3].[Boolean] = @Boolean3))))) AND EXISTS (SELECT [LPLA_3].[GUID] FROM [BAW].[dbo].[MetaData] [LPLA_3] WHERE ( [LPLA_3].[ID] = [LPA_M2].[MetadataID] AND ( [LPLA_3].[Name] = @Name4))))) Parameter: @top0 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1. Parameter: @Name2 : String. Length: 32. Precision: 0. Scale: 0. Direction: Input. Value: "Other". Parameter: @Boolean3 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: False. Parameter: @Name4 : String. Length: 32. Precision: 0. Scale: 0. Direction: Input. Value: "Metadata".

I believe whatever your data might be, the above query will always return Zero records. Try to run it directly against the database after replacing the parameter values.

Otis wrote:

Before you start posting things, I'd like to know what is it that you've trouble with? Because it seems like you're asking for something if it will work, but it's unclear what exactly and what you've tried yourself and (!) what problems that gave you.

I was unable to test is becouse I was still evaluating your product and could not download fixed binaries you ware mentioning in my first thread. As far as i know they are available in Customer area only.

This is my first project in LLBLgen. I don't know what have been done and what have not. By asking if something is possible i expected answer like "yea .. we've done it like thousands times already" or "theoretically" or even "no".

Hmm, i know exactly what i want to do. Maby i can't express myself clearly. I'll try to be more precise. Thank you for your patience with me

Now:

I tried this query before. It returns more the zero records when both @Name2 and @Name4 are equal and there is a document that has metadata named @Name2. If @Name2 and @Name4 are diferent it's always 0.

I'm not an sql guru so i can't tell why. Otis: You asked me to post generated query. So i did.

This query returns 3 rows:

SELECT DISTINCT TOP(10) COUNT(*) AS [LPAV_] FROM ((SELECT [LPLA_1].[ID], [LPLA_1].[RootID], [LPLA_1].[Type] FROM [BAW].[dbo].[MetaDocument] [LPLA_1] ) [LPA_L1] INNER JOIN [BAW].[dbo].[MetaDataInMetaDocument] [LPA_M2] ON [LPA_L1].[ID]=[LPA_M2].[DocumentID]) WHERE ( ( ( EXISTS (SELECT [LPLA_3].[GUID] FROM [BAW].[dbo].[MetaData] [LPLA_3] WHERE ( [LPLA_3].[ID] = [LPA_M2].[MetadataID] AND ( ( [LPLA_3].[Name] = 'Metadata'))))) AND EXISTS (SELECT [LPLA_3].[GUID] FROM [BAW].[dbo].[MetaData] [LPLA_3] WHERE ( [LPLA_3].[ID] = [LPA_M2].[MetadataID] AND ( [LPLA_3].[Name] = 'Metadata')))))

What I know is that (logicaly) filteredTwice in the following code should contain 2 rows. It doesn't. That's my problem.

var documents = from d in metaData.MetaDocument select d;
var filteredOnce = documents.Where(d1 => d1.MetaDatas.Any((m1) => m1.Name == "Other" && m1.Boolean == false));
var filteredTwice = filteredOnce.Where(d1 => d1.MetaDatas.Any((m1) => m1.Name == "Metadata")
var couint = filteredTwice.Count();

I expected query like this (not an exact sql but i hope it's clear).

a) SELECT * FROM MetaDocument b) SELECT * FROM a) WHERE EXISTS MetaData that have referece to examined metadocument and this metadata has field Name equal "Other" and field Boolean equal 0 c) SELECT * FROM b) WHERE EXISTS MetaData that have referece to examined metadocument and this metadata has field Name equal "Metadata" d) SELECT COUNT(*) FROM c)

I might be doing something wrong if so please tell me.

b) SELECT * FROM a) WHERE EXISTS MetaData that have referece to examiemed metadocument and this metadata has field Name equal "Other" and field Boolean equal 0 c) SELECT * FROM b) WHERE EXISTS MetaData that have referece to examiemed metadocument and this metadata has field Name equal "Metadata"

From a SQL point of view Query c) will always return Zero records. So it's not logical that it returns any number of rows.

As it appears, you have a m:n relation implemented as follows: MetaDocument---MetaDataInMetaDocument---MetaData

And the query you are running is doing the following (Simplified):

SELECT * FROM MetaDocument JOIN MetaDataInMetaDocument DID WHERE EXISTS (SELECT * FROM MetaData WHERE Name = "ABC" AND MetaData.ID = DID.MetaDataID) AND EXISTS (SELECT * FROM MetaData WHERE Name = "XYZ" AND MetaData.ID = DID.MetaDataID)

The last condition (in bold) in both of the sub-queries ties these sub Queries to the Row from the outer Select Statement. And for this specific reason, there can be now single row in the Intermediate Table (MetaDataInMetaDocument DID) that points to 2 rows in the MetaData table one with Name = "ABC" and the other with Name = "XYZ"

I think what you need to do is to get the count of the following (Simplified):

SELECT * 
FROM MetaDocument 

WHERE EXISTS 
(SELECT * FROM MetaData JOIN MetaDataInMetaDocument DID
WHERE Name = "ABC" AND DID.MetaDocumentID = MetaDocument.ID)

AND EXISTS 
(SELECT * FROM MetaData JOIN MetaDataInMetaDocument DID
WHERE Name = "XYZ" AND DID.MetaDocumentID = MetaDocument.ID)

Moving the Join of the Intermediate table to the sub-queries will secure the results for you.

Hmm, I don't understand why c) would always return zero.

This of course (i mean .. it wasn't obvious for me until now) will return zero

SELECT * FROM MetaDocument JOIN MetaDataInMetaDocument DID WHERE EXISTS (SELECT * FROM MetaData WHERE Name = "ABC" AND MetaData.ID = DID.MetaDataID) AND EXISTS (SELECT * FROM MetaData WHERE Name = "XYZ" AND MetaData.ID = DID.MetaDataID)

That's the one that LLBL generates. It's not the same query i've sketched at the end of my previous post.

Her's more like what i had in mind:

CREATE VIEW a) AS
SELECT * 
FROM MetaDocument 

CREATE VIEW b) AS
SELECT *
FROM a)
JOIN MetaDataInMetaDocument DID
WHERE EXISTS (SELECT * FROM MetaData WHERE Name = "ABC" AND MetaData.ID = DID.MetaDataID)

CREATE VIEW c) AS
SELECT *
FROM b)
JOIN MetaDataInMetaDocument DID
WHERE EXISTS (SELECT * FROM MetaData WHERE Name = "XYZ" AND MetaData.ID = DID.MetaDataID)

a), b) nad c) are all subsets of MetaDocument table.

Anyway, the query you've writen works perfectly, but there's another question: how can i make LLBL produce this query?

Instead of tagging on where calls, you should use 1 where with 'and' and 2 Any() calls for the exists subqueries.

I think (but it looks like it is the same): var q = from m in metaData.MetaDocument where m.MetaDatas.Any.((m1) => m1.Name == "Other" && m1.Boolean == false) && m.MetaDatas.Any((m1) => m1.Name == "Metadata") select m;

Well this doesn't work either. It returns similar query:

    Query: SELECT DISTINCT TOP(@top0) COUNT(*) AS [LPAV_] FROM ( [BAW].[dbo].[MetaDocument] [LPA_L1]  INNER JOIN [BAW].[dbo].[MetaDataInMetaDocument] [LPA_M2]  ON  [LPA_L1].[ID]=[LPA_M2].[DocumentID]) WHERE ( ( (  EXISTS (SELECT [LPLA_2].[GUID] FROM [BAW].[dbo].[MetaData] [LPLA_2]  WHERE ( [LPLA_2].[ID] = [LPA_M2].[MetadataID] AND ( ( [LPLA_2].[Name] = @Name2) AND ( [LPLA_2].[Boolean] = @Boolean3)))) AND  EXISTS (SELECT [LPLA_2].[GUID] FROM [BAW].[dbo].[MetaData] [LPLA_2]  WHERE ( [LPLA_2].[ID] = [LPA_M2].[MetadataID] AND ( [LPLA_2].[Name] = @Name4))))))
    Parameter: @top0 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
    Parameter: @Name2 : String. Length: 32. Precision: 0. Scale: 0. Direction: Input. Value: "Other".
    Parameter: @Boolean3 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: False.
    Parameter: @Name4 : String. Length: 32. Precision: 0. Scale: 0. Direction: Input. Value: "Metadata".

Edit: And even if it worked it's useless because it doesn't allow for dynamic nesting. I have to be able to filter by any number of metadatas. Not only by two, like in this example, or any other fixed number.

Edit2: It doesn't have to be in linq. Any working way will do.

Is it hopeless?

Appending Where calls doesn't combine the wheres into a BinaryExpression.

I think you need to look at the PredicateBuilder, If you want to combine two predicates into one BinaryExpression: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=14224 This is what the PredicateBuilder does.