How do I use the Criteria API to query primitive types in a many-to-many relationship?

Question

I have a "bookmark" entity that has tags which are strings. In C#, the bookmark Poco is similar to:

public class BookmarkEntity
{
    public virtual Guid Id { get; set; }
    public virtual ISet<string> Tags { get; set; }
}

I've automapped the entity with this override:

public class BookmarkEntityMappingOverride : IAutoMappingOverride<BookmarkEntity>
{
    public void Override(AutoMapping<BookmarkEntity> mapping)
    {
        mapping.HasManyToMany(x => x.Tags).AsSet().Element("Value").Not.LazyLoad();
    }
}

This generates these two tables:

create table "BookmarkEntity" (
    Id UNIQUEIDENTIFIER not null,
    primary key (Id)
)

create table Tags (
    BookmarkEntity_id UNIQUEIDENTIFIER not null,
    Value TEXT,
    constraint FK9061CD2928F7F2F9 foreign key (BookmarkEntity_id)
                                  references "BookmarkEntity"
)

Now, I'd like to be able to get the unique set of tags from the SQLite-backed database. Currently I'm executing this query:

SELECT DISTINCT Value FROM Tags ORDER BY Value

This does what I want, but I'd like to use the Criteria API to get it a little more strongly typed. I started with this effort:

Session.CreateCriteria<BookmarkEntity>()
       .SetProjection(
            Projections.Distinct(Projections.Property<BookmarkEntity>(b => b.Tags)))
       .AddOrder(Order.Asc(Projections.Property("Value")))
       .List<string>();

But this doesn't work because it's trying to query over the Bookmarks table. What do I need to do to get this to be similar to my hardcoded SQL query?

Are you sure, that HasManyToMany is the right mapping for just a list of strings? I would doubt, and suggest to use just HasMany... — Radim Köhler, Sep 22 '15 at 14:34
I've gone back and forth on that, `HasMany` probably makes more sense. Thought that's mostly not relevant to this question though. — Patrick Quirk, Sep 22 '15 at 14:35

score 1 · Answer 1 · edited May 23 '17 at 12:14

1

I would suggest to follow these

To filter (WHERE) via collection, we can use: construct with "MyArray.elements"

.Add(Restrictions.Eq("Tags.elements", ...));

To SELECT we have to add alias (targeting colleciton) and the query will be like this:

Session.CreateCriteria<BookmarkEntity>()
       .CreateAlias("Tags", "t")
       .SetProjection(
         // Projections.Distinct(Projections.Property<BookmarkEntity>(b => b.Tags))
         Projections.Distinct(Projections.Property("t.elements"))
       )
       .AddOrder(Order.Asc(Projections.Property("t.elements")))
       .List<string>();

ALSO, I would suggest, to use just HasMany in case of value type collection (IList<string>).

edited May 23 '17 at 12:14

Community

1
1

answered Sep 22 '15 at 14:31

Radim Köhler

122,561
47
239
335

Both of those links return the type being queried over; in my case it would be `BookmarkEntity`. But I want to get the `string`s returned instead... – Patrick Quirk Sep 22 '15 at 14:37
I most likely did not understood well to the title *"How do I use the Criteria API to **query** primitive types.."* ... I thought you need to use that for WHERE clause... – Radim Köhler Sep 22 '15 at 14:39
Ah, no. I am just looking for an equivalent Criteria API construct for the SQL statement in my question. – Patrick Quirk Sep 22 '15 at 14:41
I added the SELECT solution, which will give you all the tags... as a distinct list – Radim Köhler Sep 22 '15 at 17:20
Your edit is pretty close. It returns the correct values but performs an inner join on the BookmarkEntity and Tags tables. Is this possible to avoid? – Patrick Quirk Sep 22 '15 at 18:52
No. The answer is NO. Sorry for that... the reason is - we are using ORM ... so all is driven by mapping. But in your case, I would use **raw** sql `session.CreateSQLQuery("SELECT DISTINCT Value FROM Tags ORDER BY Value")`. And a NOTE. My general approach is always promote each setting in the system to first class citizen, and map it as an *Entity*. At the end, I do have only `IList` and can query `SomeEntity` directly. And also - I would never use many-to-many e.g. [here](http://stackoverflow.com/a/22563990/1679310) – Radim Köhler Sep 22 '15 at 19:02

How do I use the Criteria API to query primitive types in a many-to-many relationship?

1 Answers1