LINQ multiple joins with different objects

Question

I've been working on a C# program that use LINQ to manage a simple SQLite database. The database consists in different tables that in my code are represented by different classes. This is how I create the tables:

    public ITable<Doctors> Doctors => GetTable<Doctors>();

    public ITable<Patients> Patients=> GetTable<Patients>();

    public ITable<Prescriptions> Prescriptions=>GetTable<Prescriptions>();

    public ITable<Assistants> Assistants=> GetTable<Assistants>();

    public ITable<Medicines> Medicines => GetTable<Medicines>();

    public ITable<General_medicines> General_medicines=> GetTable<General_medicines>();

    public ITable<Stocks> Stocks=> GetTable<Stocks>();

    public ITable<Dosages> Dosages=> GetTable<Dosages>();

    public ITable<Recipes> Recipes=> GetTable<Recipes>();

    public ITable<Prescriptions_MP> Prescriptions_MP=> GetTable<Prescriptions_MP>();

Now, I want to create a LINQ query (in a separate class) where I get different properties in all these tables and I put them inside an IEnumerable that I can later scan. To do so, I proceed as following:

public IEnumerable<Therapy> TakePrescriptions()
{
            HealthDataContext DbContext = DbFactory.Create();
            var dbPrescriptions = DbContext.GetTable<Prescriptions>();
            IEnumerable<Prescriptions> prescriptions= dbPrescriptions.AsEnumerable();

            var dbPatients= DbContext.GetTable<Patients>();
            IEnumerable<Pazienti> patients= dbPatients.AsEnumerable();

            var dbPrescrizioniMP = DbContext.GetTable<Prescriptions_MP>();
            IEnumerable<Prescriptions_MP> prescriptionsMP = dbPrescriptionsMP .AsEnumerable();

            var dbRecipes = DbContext.GetTable<Recipes>();
            IEnumerable<Recipes> recipes= dbRecipes .AsEnumerable();

            var dbMedicines= DbContext.GetTable<Medicines>();
            IEnumerable<Medicines> medicines= dbMedicines.AsEnumerable();

            var dbGeneral_medicines = DbContext.GetTable<General_medicines>();
            IEnumerable<General_medicines> general_medicines= dbGeneral_medicines.AsEnumerable();

            var dbDosages = DbContext.GetTable<Dosages>();
            IEnumerable<Dosages> dosages= dbDosages .AsEnumerable();

            var query = from p in patients
                            join pr in prescriptions_MP on p.Id equals pr.Patient
                            join pre in prescriptions on pr.Prescription equals pre.Id
                            join fc in medicines on pre.Medicine equals fc.Id
                            join fg in general_medicines on fc.Medicine equals fg.Id
                            join ds in dosages on fg.Id equals ds.General_Medicine
                            where p.Doctor== IdDoctor
                            select new
                            {
                                IdDoctor, //int
                                p.Name, //string
                                pr.Prescription, //int
                                pre.Id, //int
                                fc.Format, //string 
                                fc.Administration, //string
                                fc.Downloadable, //boolean
                                fc.Full_stomach, //boolean
                                nameM= fg.Name, //string
                                ds.Quantity, //int
                                ds.Hour //string
                            };


            List < Therapy> therapy = new List<Therapy>();



            foreach(var object in query)
            {
                Therapy t = new Therapy(IdDoctor, object.Name, object.Prescription, object.Id, object.Format, object .Administration, object.Downloadable, object.Full_stomach, object.nameM, object.Quantity, object.Hour);

                therapy.Add(t);

            }

            return therapy;
}

Now when I try to load the page that should display a list of the results, I get InvalidOperationException: An open reader is associated with this command. Close it before changing the CommandText property. at the foreach operation.

When I try to debug, I can see that the tables I created before the query have items inside, but the result of the query is NULL.

I tried to dispose of the DBContext but then I get this exception: ObjectDisposedException: IDataContext is disposed, see https://github.com/linq2db/linq2db/wiki/Managing-data-connection Object name: 'DataConnection'.

Answer 1

You should remove AsEnumerable() calls from tables you use in query, because they force linq2db to execute them as separate queries.

This has two consequences:

• it attempts to start multiple queries over single db connection, which is not supported and you get InvalidOperationException
• let's imagine it will work (e.g. you replaced AsEnumerable() with ToList() to read all data from each query). In this case it will load all data into application and perform joins in C# code - it will lead to really bad performance, especially in cases when you need to discard some data that doesn't meet join conditions.

Answer 2

The error you are getting “An open reader is associated with this command. Close it before changing the CommandText property”, suggests that multiple readers are open. However looking at your query it seems like one reader is open for your one query. The reality however is different. You have 5 tables, such that each has a 1 to many relationship to another table. For example table patient has a 1 to many relationship to prescriptions table. As a patient can have multiple prescriptions.

Thus just considering these two tables, we first have one query to load all the patients, and then another query per patient to load all of its prescriptions, this means if you have N patients, this translates to 1 + N query , 1 to load all the patients, and N to load prescriptions of each of these patients.

Now given the 5- level join that you have in the code, doing the math, you can see how many potential open readers are out there. The data is loaded on demand, meaning the readers are activated once you iterate through the results of your query, this is to avoid huge memory usage, in the cost of performance, and hence when in your foreach loop you start to iterate through the objects, the data is for real being fetched.

To solve the problem, you can try converting ToList at the end of your query to encourage binding (thus eager loading), or as one of the commenters are suggesting pass MultipleActiveResultSets=true to your connection string.