nhibernate map item order issue

Question

Hi all i need some ideas how to fix order by map element issue with nullable items.

I am trying to do sorting

IQueryable<Device> q;
q = from d in q
orderby d.Attributes[1]
orderby d.Name
select d; //<-- return only devices where device_attrib_value (d.Attributes[1])
//not null

The problem in result query its not return elements without device_attrib_value.If look down you can see sql i think it wrong becouse there must be some join expression.

The question: How can i change linq expression or mapping to provide good attribute sorting? I need it for dynamic columns in my grid with sorting and filtering

Appreciate for any help or ideas!

Database

Simple database

Mapping file

< class name="Device" table="DEVICES" lazy="false">   
    <id name="Id">
      <column name="ID" />
      <generator class="native" />
    </id>    
    <property name="Name" column="NAME"/>       
    <map name="Attributes" table="DEVICE_ATTRIB_VALUES">
        <key column="DEVICE_ID"  not-null="true"/>
        <index column="ATTRIB_ID" type="System.Int64" />
        <element column="VALUE" type="System.String"/>
    </map>    
  </class>

Device entity class

public class Device
{        
    public virtual long Id { get; set; }
    public virtual string Name { get; set; }        
    public virtual IDictionary<long, string> Attributes { get; set; }        
}

Sql generated by NHibernate

select
        device0_.ID as ID1_,
        device0_.NAME as NAME1_
    from
        DEVICES device0_,
        DEVICE_ATTRIB_VALUES attributes1_
    where
        device0_.ID=attributes1_.DEVICE_ID
        and attributes1_.ATTRIB_ID = @p0
    order by
        attributes1_.VALUE asc,
        device0_.NAME asc;

Anas Jaber · Answer 1 · 2012-01-23T13:33:04.507

0

In the past I had this problem and I remember to solve this issue you must include Attributes in select list to retrieve all items whether nullable or not.

edited Jan 23 '12 at 13:33

answered Jan 22 '12 at 09:05

Anas Jaber

583
1
7
20

Hi thanks for response but i need to return IQueryable q. – Sanja Melnichuk Jan 22 '12 at 09:13
if include d.Attributes in select list, q will form sql statement that include columns in DEVICE_ATTRIB_VALUES that you need for sorting and paging and filtering. – Anas Jaber Jan 22 '12 at 09:25

score 0 · Answer 2 · answered Jan 22 '12 at 21:12

If you can replace map with bag in your mapping then I can suggest the following solution:

public class Attrib
{
    public virtual long Id { get; set; }
    public virtual string Value { get; set; }
}

<class name="Device" table="DEVICES" lazy="false">
...
    <bag name="Attributes" table="DEVICE_ATTRIB_VALUES">
        <key column="DEVICE_ID" not-null="true"/>
        <composite-element class="Attrib">
            <property name="Id" column="ATTRIB_ID" type="System.Int64" />
            <property name="Value" column="VALUE" type="System.String"/>
        </composite-element>
    </bag>
...
</class>

and this LINQ querty will include devices even if they don't have the specified attribute

session.Query<Device>().OrderBy(d => d.Attributes.Where(a => a.Id == 1).Select(a => a.Value)).ToList();

this query produces following SQL

select
    device0_.ID as ID0_,
    device0_.NAME as NAME0_
from
    DEVICES device0_
order by
    (select 
         attributes1_.VALUE
    from
        DEVICE_ATTRIB_VALUES attributes1_
    where
        device0_.ID=attributes1_.DEVICE_ID
        and attributes1_.ATTRIB_ID=@p0) asc;

Hope this help you to find more elegant solution.

nhibernate map item order issue

2 Answers2