动态DBSet查询和查询 [英] Dynamic DBSet lookup and query

问题描述

我有一个部分结果视图，其中包含表的名称和要查询的特定列的值。我读了DBContext API，发现Set（Type）应该返回一个可以进行CRUD操作的DBSet。我不知道如何查询DBSet没有PK，因为用户不知道PK查找。

I have a partial result view that takes in the name of the table and a value for a particular column to query. I read the DBContext API and found that Set(Type) should return a DBSet that you can do CRUD operations on. I don't know how exactly to query the DBSet without a PK since the user don't know the PK to look up.

可能使用经典ADO会更容易？

May be using Classic ADO would be easier?

编辑：我找出如何使用DbSet.SQLQuery函数，但没有线索来存储结果。我检查了调试器中的元素，SQLQuery确实可以发现表中的所有行。

I figure out how to use DbSet.SQLQuery function but have no clue to store the results. I inspected the element in debugger and the SQLQuery does work as it found all the rows inside the table.

public class SF1DB : DbContext
{
    //List of table names that feeds a DropDownList
    public DbSet<tablelist> tables { get; set; }

    //Data table
    public DbSet<dataTable1> dataTable1 { get; set; }
    public DbSet<dataTable2> dataTable2 { get; set; }
    //...list of other tables
}

public PartialViewResult GetFeatures(String tablelist, String[] countyfp)
{
    String type = "MvcApplication1.Models." + tablelist;
    Type dbType = Type.GetType(type);
    DbSet set = _db.Set(dbType);
    String sql = "select * from " + tablelist;

    //How do I store the result in a variable?
    set.SqlQuery(sql);
    return PartialView();
}

推荐答案

一个与用户选择的DbSet类型相同的列表。然后我使用SQLQuery的GetEnumerator方法，并通过结果循环并添加到新列表。最后，将列表传递给部分视图。

I figured it out by creating a List that have the same type as the DbSet that the user selected. Then I use the SQLQuery's GetEnumerator method and iterate thru the result and add to the new list. Finally, pass the list to the partial view.

public PartialViewResult GetFeatures(String tablelist, String[] countyfp)
{
    String type = "MvcApplication1.Models." + tablelist;
    Type dbType = Type.GetType(type);
    DbSet set = _db.Set(dbType);
    String sql = "select * from " + tablelist + " where ";
    Type listType = typeof(List<>).MakeGenericType(dbType);
    IList list = (IList)Activator.CreateInstance(listType);

    for (int i = 0; i < countyfp.Length; i++)
    {
        sql += "cntyidfp like '%" + countyfp[i] + "'";
        if (i < (countyfp.Length - 1))
        {
            sql += " or ";
        }
    }

    IEnumerator result = set.SqlQuery(sql).GetEnumerator();
    while (result.MoveNext())
    {
        list.Add(result.Current);                
    }

    return PartialView(list);
}

这篇关于动态DBSet查询和查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！