CODEWITHSUNDEEP
c#winformsentity-framework
Estevao Santiago
Estevao Santiago

Reputation: 865

Should I re-utilize my EF query method and if yes, how to do it

I am using EF to get data from my MySQL database.

Now, I have two tables, the first is customers and project_codes.

The table project_codes have a FK to customers named id_customers. This way I am able to query which projects belong to a customer.

On my DAL, I got the following:

public List<customer> SelectAll()
{
    using (ubmmsEntities db = new ubmmsEntities())
    {
        var result = db.customers
                     .Include(p => p.project_codes)
                     .OrderBy(c=>c.customer_name)
                     .ToList();
        return result;

    }
}

That outputs to me all my customer and their respective project_codes.

Recently I needed to only get the project codes, so I created a DAL to get all the project codes from my database. But then I got myself thinking: "Should I have done that? Wouldn't be best to use my SelectAll() method and from it use Linq to fetch me the list of project_codes off all customers?

So this that was my first question. I mean, re-utilizing methods as much as possible is a good thing from a maintainability perspective, right?

The second question would be, how to get all the project_codes to a List? Doing it directly is easy, but I failed to achieve that using the SelectAll() as a base.

It worked alright if I had the customer_id using:

    ddlProject.DataSource = CustomerList.Where(x => x.id.Equals(customer_id))
.Select(p => p.project_codes).FirstOrDefault();

That outputed me the project codes of that customer, but I tried different approaches (foreach, where within whhere and some others at random) but they either the syntax fail or don't output me a list with all the project_codes. So that is another reason for me going with a specific method to get me the project codes.

Even if "common sense" or best practices says it is a bad idea to re-utilize the method as mentioned above, I would like some directions on how to achieve a list of project_codes using the return of SelectAll()... never know when it can come in hand.

Let me know your thoughts.

Upvotes: 1

Views: 30

Answers (1)

BradleyDotNET
BradleyDotNET

Reputation: 61379

There's a trade-off here; you are either iterating a larger collection (and doing selects, etc) on an in-memory collection, or iterating a smaller collection but having to go to a database to do it.

You will need to profile your setup to determine which is faster, but its entirely possible that the in-memory approach will be better (though stale if your data could have changed!).

To get all the project_codes, you should just need:

List<customer> customers; //Fill from DAL
List<project_code> allProjects = customers.SelectMany(c => c.project_codes).ToList();

Note that I used SelectMany to flatten the hierarchy of collections, I don't think SelectAll is actually a LINQ method.

Upvotes: 1

Related Questions