c# - tables - linq sum multiple columns




many-to-many with extra columns nhibernate (4)

Im not sure if this is what you need:

<bag name="files_attrs" table="files_attrs" lazy="true" where="something like '%mode = read-only%' and something like '%view = visible%'">
    <key column="attr_id" />
    <one-to-many class="Files_Attrs" />
</bag>

Where something is the attribute or is the column where is the data to filter.

Try this query:

Files fAlias = null;
Attrs aAlias = null;

var disjunction = new Disjunction();

disjunction.Add(Restrictions.On(() => aAlias.value)
    .IsLike("mode = read-only", MatchMode.Anywhere));
disjunction.Add(Restrictions.On(() => aAlias.value)
    .IsLike("view = visible", MatchMode.Anywhere));

var subquery = QueryOver.Of<Files_Attrs>
    .Inner.JoinAlias(x => x.file, () => fAlias)
    .Inner.JoinAlias(x => x.attr, () => aAlias)
    .Where(disjunction)
    .Select(() => fAlias);

var files = session.QueryOver<Files>
    .WithSubquery.WhereExists(subquery)
    .List();

So I have two tables Users and Groups. These tables are (in the database) liked with a UGlink link table. Now except for the primary-foreign keys the link table have an extra column: Date.

From what I understand this means that I have to have two Many-to-One, with the link "in the middle".

However since I'm almost never is interested of the extra column value, is there anyway to avoid the link? That is I want to be able to write:

thisUser.Groups

to get the groups, instead of:

thisUser.UGlinks.Group


The many-to-many, without the explicit mapping of the pairing table as an entity - is in NHibernate of course suported. So, in case, that the Date column is autogenerated, or nullable (does not have to be inserted by app/NHiberante), we can do it like here: 6.8. Bidirectional Associations

<class name="User">
    <id name="Id" column="Uid"/>
    ...
    <bag name="Groups" table="UGlink" lazy="true">
        <key column="Uid"/>
        <many-to-many class="Group" column="Gid"/>
    </bag>
</class>

<class name="Group">
    <id name="id" column="Gid"/>
    ...

    <!-- inverse end -->
    <bag name="Users" table="UGlink" inverse="true" lazy="true">
        <key column="Gid"/>
        <many-to-many class="User" column="Uid"/>
    </bag>
</class>

So, what we have is a mapping, in which NHiberante does care about the pairing table, and we can do:

thisUser.Groups

But if I could suggest, do not go with many-to-many. The many-to-one with pairing object is (I'd say) better solution, because it will support searching Users by Groups and vice versa.

See Chapter 24. Best Practices, cite:

Don't use exotic association mappings.

Good usecases for a real many-to-many associations are rare. Most of the time you need additional information stored in the "link table". In this case, it is much better to use two one-to-many associations to an intermediate link class. In fact, we think that most associations are one-to-many and many-to-one, you should be careful when using any other association style and ask yourself if it is really neccessary.

Here is some more detailed explanation how to do it without many-to-many: Nhibernate: How to represent Many-To-Many relationships with One-to-Many relationships?


NHibernate: could not initialize a collection:

The issue should/could be in the <key> mapping:

<bag name="Keywords" table="StatueKeyword" lazy="false">
  <!-- <key> is representing column where current Statue ID should be searched 
   while the below one seems to be the ID column of the pairing table
   so instead of this
  <key column="IdStatueKeyword"/>
   use this: -->
  <key column="IdStatue"/>
  <many-to-many class="Keyword" column="IdKeyword"/>
</bag>

Also check these:

Small cite:

The foreign key from the collection table to the table of the owning class is declared using a <key> element.

Another tip, if you do have an ID column of the pairing table, you should try to use augmented feature:

Another cite from doc about idbag:

Note that the update performance of an <idbag> is much better than a regular <bag>! NHibernate can locate individual rows efficiently and update or delete them individually, just like a list, map or set.

Finally, I (personally) would descourage you from using many-to-many. My view is, that it is better to avoid it. See: 24. Best Practices (a cite:)

Don't use exotic association mappings.

Good usecases for a real many-to-many associations are rare. Most of the time you need additional information stored in the "link table". In this case, it is much better to use two one-to-many associations to an intermediate link class. In fact, we think that most associations are one-to-many and many-to-one, you should be careful when using any other association style and ask yourself if it is really neccessary.

Maybe later also check these:


Nhibernate: How to represent Many-To-Many relationships with One-to-Many relationships?

I just come up to that question, and realized, that there is missing any answer. And it is a shame, while I do often point out this NHibernate documentation statement: 24. Best Practices

Don't use exotic association mappings.

Good usecases for a real many-to-many associations are rare. Most of the time you need additional information stored in the "link table". In this case, it is much better to use two one-to-many associations to an intermediate link class. In fact, we think that most associations are one-to-many and many-to-one, you should be careful when using any other association style and ask yourself if it is really neccessary.

Take a look at the example under the 23.2. Author/Work. Extract, the simplified version of the many-to-many relation between Author and Work:

<class name="Work" table="works" ...>
        <id name="Id" column="id" generator="native" />
        ...
        <set name="Authors" table="author_work" lazy="true">
            <key>
                <column name="work_id" not-null="true"/>
            </key>
            <many-to-many class="Author">
                <column name="author_id" not-null="true"/>
            </many-to-many>
        </set>
</class>

And its many-to-many target Author:

<class name="Author" table="authors">
  ...
  <set name="Works" table="author_work" inverse="true" lazy="true">
     <key column="author_id"/>
     <many-to-many class="Work" column="work_id"/>
  </set>
</class>

So, if we would like to order the set of Works on load, we do have a problem. There is no column in the pair table. But what's more important, there is no way how to manage such a column.

What we can do, is to introduced the Pair object: AuthorWork and extend the Pair table as needed

public class AuthorWork
{

    public virtual Author Author { get; set; }
    public virtual Work Work { get; set; }
    public virtual int OrderBy { get; set; }
}

Mapping of the AuthorWork

<class name="AuthorWork" table="author_work">
    ...
    <many-to-one name="Author" column="author_id" />
    <many-to-one name="Workr"  column="work_id" />
    <property name="OrderBy" />

Having this we can convert the many-to-many mapping to one-to-many, for example the Authors collection:

<set name="Authors" lazy="true"
  order-by="OrderBy">
  <key column="work_id" not-null="true"/>
  <one-to-many class="AuthorWork" />
</set>

And we can manage the entity AuthorWork, set the OrderBy column, and therefore effectively work with the pairing table.

NOTE: have to agree with that suggestion in docsumentation The more requirements come, the more happy we are that we do have a way how to manage the relation!





nhibernate