将ESQL转换为LINQ to Entities。新新旗新新新旗新新新旗新新旗新新旗新新旗新新旗新新200新新 [英] Converting ESQL to LINQ to Entities. Sort by related entities

问题描述

我正在使用EF + RIA，不幸的是遇到一些相关实体进行排序的问题。
为此，我实现了ESQL查询（仅发现此解决方案）：

I am using EF + RIA and unfortunately meet some problems with sorting by related entities. For such purpose there is 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));                                                        

表具有以下结构：

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

<SomeEntity>:
    int Id;
    string Name;  

有没有办法使用LINQ to Entities方法重写这个东西（排序）？

Is there any way to rewrite this stuff(sorting), using LINQ to Entities approach?

推荐答案

这是我的尝试，我不能保证它会工作。 我需要考虑如何获取一个动态列名，我不知道那个。编辑：您可以使用字符串作为订单列。

Here's my attempt, I can't guarantee it will work. I need to think more on how to get a dynamic column name, I'm not sure on that one. you can use a string for the 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（）; //调用toList或枚举来执行查询，因为LINQ已经延迟执行。

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

编辑：我认为在选择停止后的排序是从排序。我把select语句移到了order之后。我还添加了query =，但我不知道是否需要这个。我没有办法在这个时候进行测试。

I think that ordering after the select stops is from ordering by. I moved the select statement to after the order by. I also added the "query =", but I'm not sure if that is needed. I don't have a way to test this at the moment.

编辑3：我开除了 LINQPad 今天对此进行了一些调整。我使用代码优先的方式对您的数据进行建模，使用EF，并且应该与您拥有的接近。
如果您只想获取属性列表（您不是），则此方法效果会更好。为了解决这个问题，我添加了一个Entity属性到MyAttribute类。
此代码适用于LINQPAD。

EDIT 3: I fired up LINQPad today and made a few tweaks to what I had before. I modeled your data in a Code-first approach to using EF and it should be close to what you have. This approach works better if you're just trying to get a list of Attributes (which you aren't). To get around that I added an 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);
}

这篇关于将ESQL转换为LINQ to Entities。新新旗新新新旗新新新旗新新旗新新旗新新旗新新旗新新200新新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！