Convert ESQL to LINQ into objects. Sort by related objects

Question

I use EF + RIA and, unfortunately, I encounter some problems with sorting by related objects. For this purpose, there is an ESQL query that I implemented (found only this solution):

var queryESQL = string.Format( @" select VALUE ent from SomeEntities as ent join Attributes as ea ON ea.EntityId = ent.Id where ea.AttributeTypeId = @typeId order by ea.{0} {1}", columnName, descending ? "desc" : "asc"); var query = ObjectContext.CreateQuery<SomeEntity>(queryESQL, new ObjectParameter("typeId", attributeTypeId)); 

Tables have the following structure:

 <Attribute>: int Id; decimal DecimalColumn; string StringColumn; int EntityId; int AttributeTypeId; <SomeEntity>: int Id; string Name; 

Is there a way to rewrite this stuff (sort) using the LINQ to Entities approach?

Answer 1

Here is my attempt, I can not guarantee that this will work. I need to think more about how to get a dynamic column name, I'm not sure about that. EDIT: you can use row for order column.

 int typeId = 1115; bool orderAscending = false; string columnName = "StringColumn"; var query = from ent in SomeEntities join ea in Attributes on ea.EntityId = ent.Id where ea.AttributeTypeId = typeId; if(orderAscending) { query = query.OrderBy(ea => columnName).Select(ea => ea.Value); } else { query = query.OrderByDescending(ea => columnName).Select(ea => ea.Value); } 

var results = query.ToList (); // call toList or list to execute the query because LINQ has deferred execution.

EDIT: I think the ordering after the selection stops when ordering. I moved the select statement after ordering. I also added "query =", but I'm not sure if this is necessary. At the moment, I have no way to verify this.

EDIT 3: I released LINQPad today and made a few tweaks to what I had before. I modeled your data in a code-based approach to using EF, and it should be close to what you have. This approach works better if you are just trying to get a list of attributes (which you are not). To get around this, I added the Entity property to the MyAttribute class. This code works in LINQPAD.

 void Main() { // add test entities as needed. I'm assuming you have an Attibutes collection on your Entity based on your tables. List<MyEntity> SomeEntities = new List<MyEntity>(); MyEntity e1 = new MyEntity(); MyAttribute a1 = new MyAttribute(){ StringColumn="One", DecimalColumn=25.6M, Id=1, EntityId=1, AttributeTypeId = 1, Entity=e1 }; e1.Attributes.Add(a1); e1.Id = 1; e1.Name= "E1"; SomeEntities.Add(e1); MyEntity e2 = new MyEntity(); MyAttribute a2 = new MyAttribute(){ StringColumn="Two", DecimalColumn=198.7M, Id=2, EntityId=2, AttributeTypeId = 1, Entity=e2 }; e2.Attributes.Add(a2); e2.Id = 2; e2.Name = "E2"; SomeEntities.Add(e2); MyEntity e3 = new MyEntity(); MyAttribute a3 = new MyAttribute(){ StringColumn="Three", DecimalColumn=65.9M, Id=3, EntityId=3, AttributeTypeId = 1, Entity=e3 }; e3.Attributes.Add(a3); e3.Id = 3; e3.Name = "E3"; SomeEntities.Add(e3); List<MyAttribute> attributes = new List<MyAttribute>(); attributes.Add(a1); attributes.Add(a2); attributes.Add(a3); int typeId = 1; bool orderAscending = true; string columnName = "StringColumn"; var query = (from ent in SomeEntities where ent.Attributes.Any(a => a.AttributeTypeId == typeId) select ent.Attributes).SelectMany(a => a).AsQueryable(); query.Dump("Pre Ordering"); if(orderAscending) { // query = is needed query = query.OrderBy(att => MyEntity.GetPropertyValue(att, columnName)); } else { query = query.OrderByDescending(att => MyEntity.GetPropertyValue(att, columnName)); } // returns a list of MyAttributes. If you need to get a list of attributes, add a MyEntity property to the MyAttribute class and populate it var results = query.Select(att => att.Entity).ToList().Dump(); } // Define other methods and classes here } class MyAttribute { public int Id { get; set; } public decimal DecimalColumn { get; set; } public string StringColumn { get; set; } public int EntityId { get; set; } public int AttributeTypeId { get; set; } // having this property will require an Include in EF to return it then query, which is less effecient than the original ObjectQuery< for the question public MyEntity Entity { get; set; } } class MyEntity { public int Id { get; set; } public string Name { get; set; } public ICollection<MyAttribute> Attributes { get; set; } public MyEntity() { this.Attributes = new List<MyAttribute>(); } // this could have been on any class, I stuck it here for ease of use in LINQPad // caution reflection may be slow public static object GetPropertyValue(object obj, string property) { // from Kjetil Watnedal on http://stackoverflow.com/questions/41244/dynamic-linq-orderby System.Reflection.PropertyInfo propertyInfo=obj.GetType().GetProperty(property); return propertyInfo.GetValue(obj, null); }