用于多个数据库的LINQ查询 c#

Question

用于多个数据库的LINQ查询 c#

3

我没有找到任何关于使用LINQ查询多个数据库的好资料。我一直在使用连接字符串来在数据库之间切换并显示用户数据，现在我想实现一个搜索功能，它可以查询所有数据库并返回一个列表，而不是事先选择数据库。

这是目前我拼凑出来的代码，它只能从一个数据库返回一个列表，显然不是我想要的结果。

 public ActionResult getCustomers(string cust)
    {
        List<trakman_Entities> teInstances = new List<trakman_Entities>();
        IEnumerable<customer> customers = null;


        for (var i = 1; i < ConfigurationManager.ConnectionStrings.Count; i++)
        {

            if (ConfigurationManager.ConnectionStrings[i].ConnectionString.ToLower().Contains("metadata"))
            {
                string con = ConfigurationManager.ConnectionStrings[i].ConnectionString;
                teInstances.Add(new trakman_Entities(con));

            }

        }

        foreach (trakman_Entities entitiy in teInstances)
        {
            customers = entitiy.customers.Where(c => c.code.StartsWith(cust));

        }

        foreach(customer c in customers)
        {
            Response.Write(c.code);
            Response.Write(c.name);

        }

        Response.End();
        return View(customers);
    }

- Jed I

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- ChaseMedallion · Accepted Answer

问题在于您在foreach循环中不断重新分配客户变量：

    foreach (trakman_Entities entitiy in teInstances)
    {
        // overwrites on each iteration!
        customers = entitiy.customers.Where(c => c.code.StartsWith(cust));

    }

相反，考虑：

var customers = teInstances.SelectMany(e => e.customers.Where(c => c.code.StartsWith(cust))) 
    .ToList();

或者，使用单个LINQ查询完成整个操作：

// start with the list of connection string settings cast to IEnumerable<T>
var customers = ConfigurationManager.ConnectionStrings.Cast<ConnectionStringSettings>()
    // filter to the relevant connection strings
    .Where(s => s.ConnectionString.ToLower().Contains("metadata"))
    .SelectMany(s => {
         // for each connection string, select a data context        
         using( var context = new trakman_entities(s.ConnectionString)) {
             // for each context, select all relevant customers
             return context.customers.Where(c => c.code.StartsWith(cust)).ToArray();
         } // and dispose of the context when we're done with it
     })
    .ToList();