Databases
StackOverflow2013
Postsdbo
PONHibernate Subquery Linq - How to select groups that contain a certain item by id
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PONHibernate Subquery Linq - How to select groups that contain a certain item by id
    primarykey
    data
    text
    <p><strong>Short Version</strong> This query works in the database but fails with Linq To NHibernate. Why?</p> <pre><code>var items = (from g in db.Find&lt;DataGroupInfo&gt;() where (from d in g.data where d.Id == dataID select d).Count() &gt; 0 select g).ToList(); </code></pre> <p><strong>Detailed Long Version</strong></p> <p>I have two objects mapped by NHibernate Automapper with a ManyToMany relationship.</p> <pre><code>return Fluently.Configure() //a few other settings here... //relevant settings below .Override&lt;DataGroupInfo&gt;(map =&gt; { map.HasManyToMany(d =&gt; d.data); } .Conventions.Add(DefaultCascade.All()) .Conventions.Add(DefaultLazy.Never()) public class DataInfo { public virtual int Id { get; private set; } public virtual DateTime created { get; set; } public virtual string label { get; set; } public virtual string description { get; set; } } public class DataGroupInfo { public virtual Int32 Id { get; set; } public virtual DateTime created { get; set; } public virtual string label { get; set; } public virtual string description { get; set; } public virtual IList&lt;DataInfo&gt; data { get; set; } } </code></pre> <p>I want to grab all the DataGroups that contain a certain DataInfo. </p> <p>This query works in SQLite admin so I think my database is properly setup:</p> <pre><code>select * from DataGroupInfo INNER JOIN DataInfoToDataGroupInfo ON DataGroupInfo.Id == DataInfoToDataGroupInfo.DataGroupInfo_id where DataInfoToDataGroupInfo.DataInfo_id == 3 </code></pre> <p>I'm a repository that exposes Linq in these two ways</p> <pre><code>public IQueryable&lt;T&gt; Find&lt;T&gt;() { return session.Linq&lt;T&gt;(); } public IQueryable&lt;T&gt; Find&lt;T&gt;(Expression&lt;Func&lt;T, bool&gt;&gt; predicate) { return Find&lt;T&gt;().Where(predicate); } </code></pre> <p>I use the repositories like this </p> <pre><code> static public List&lt;DataGroupInfo&gt; GetAllWithData(Int32 dataID) { using (var db = new DBRepository()) { //var items = (from g in db.Find&lt;DataGroupInfo&gt;() // where (from d in g.data where d.Id == dataID select d).Count() &gt; 0 // select g).ToList(); var items = db.Find&lt;DataGroupInfo&gt;(dg =&gt; dg.data.Where(d =&gt; d.Id == dataID).Count() &gt; 0 ).ToList(); return items; } } </code></pre> <p>When the Linq tries to execute above I get the following error under either method:</p> <blockquote> <p>NHibernate.QueryException was unhandled by user code<br> Message="could not resolve property: data.Id of: MapLarge.Public.Data.DataGroupInfo"<br> Source="NHibernate" StackTrace: at NHibernate.Persister.Entity.AbstractPropertyMapping.GetColumns(String propertyName)</p> </blockquote> <p>What am I doing wrong?</p> <p>----Update-----</p> <p>This work around gives the expected result, but its a brute force approach pulling every datagroup into memory and then filtering objects using c# rather than the database.. but at least it narrows down my problem to a NHibernate specific issue. </p> <pre><code> var step1 = db.Find&lt;DataGroupInfo&gt;().ToList(); var items = step1.Where(dg =&gt; dg.data.Where(d =&gt; d.Id == dataID).Count() &gt; 0).ToList(); </code></pre> <p>I'd still really love an answer if anyone is out there :-)</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USGlenn
    1. USGlenn
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PONHibernate Subquery Linq - How to select groups that contain a certain item by id
      1. USGlenn
      1. VTFavorite
    2. VO
      singulars
      1. PONHibernate Subquery Linq - How to select groups that contain a certain item by id
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhat does your DataInfoToDataGroupInfo table look like? I've had problems with the Automapper joining many-to-many when the linking table contains columns other than the two foreign keys.
      singulars
      1. PONHibernate Subquery Linq - How to select groups that contain a certain item by id
      1. USJim Schubert
    2. CODataInfoToDataGroupInfo just has the two foreign keys, and the entire db was actually created by Nhibernate AutoMapper so I think it should be solid if my class definitions conform to the NHibernate Automapper conventions. Here's what SQLite Admin shows for the table's properties when I check it externally: ColumnInfoToColumnGroupInfo ColumnGroupInfo_id INTEGER not null ColumnInfo_id INTEGER not null Here's the autogen code: var newDB = new SchemaExport(config).Create(true, true);
      singulars
      1. PONHibernate Subquery Linq - How to select groups that contain a certain item by id
      1. USGlenn
    3. COI know Linq to NHibernate is fairly new, maybe this is a bug or unsupported query type?
      singulars
      1. PONHibernate Subquery Linq - How to select groups that contain a certain item by id
      1. USGlenn
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload