LINQ query for multiple databases c#

Question

I have not found any good material for querying multiple databases using LINQ. I have been using connection strings to change between databases and display data for user, now i want to implement a search function which query's all databases and returns a list instead of selecting the database before hand.

Here's what I've thrown together so far. which returns one list from one database which obviously is not what i want.

 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);
    }

ChaseMedallion · Accepted Answer · 2013-07-04 15:23:45Z

1

The problem is that you keep reassigning the customers variable in your foreach loop:

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

    }

Instead, consider:

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

Or, do do the whole thing using a single LINQ query:

// 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();

edited Jul 4, 2013 at 15:23

answered Jul 4, 2013 at 15:14

ChaseMedallion

22k18 gold badges96 silver badges164 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jed I Over a year ago

I know lol, Im a bit stuck on implementing a list containing a list of customers.

ChaseMedallion Over a year ago

@Simon Did you try my suggestion?

Guru Stron Over a year ago

@ChaseMedallion better customers.Add(...) where customers is List

Jed I Over a year ago

That is bringing back what looks to be right, just need to check results.

Collectives™ on Stack Overflow

LINQ query for multiple databases c#

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related