VS2010 SQL C# Entities: Accessing multiple tables to get single data? Foreach or Linq?

I have a small database that requires multiple table queries to retrieve data. I have written nested foreach loops to get this done but was wondering if it is too demanding on memory or there is a more efficient way to do this. I have seen some linq code to join tables together and it looks promising, but for now the foreach was easier to understand. Should I seek out linq code to replace the nested foreach loops. Thank you

student is this so sid is this.getId
enrolls - sid(k) -term(k) -year(k) -crn(k)
sections -term(k) -year(k) -crn(k) -cprefix -cno
courses -cprefix(k) -cno(k) -chours

// count up enrolled course hours
        decimal enrolledHours = 0;
        foreach (enroll e in ent.enrolls)
        {
            if (e.sid.ToString() == this.get_Id && e.term.ToString() == term &&
                    e.year.ToString() == year)   // find enrolls for student
            {
                foreach (section s in ent.sections)  // search for cno for crn
                {
                    if (s.crn == e.crn && s.term == e.term && s.year == e.year)
                    {
                        foreach (cours c in ent.courses)    // match section and course
                        {
                            if (c.cprefix == s.cprefix && c.cno == s.cno)
                            {
                                enrolledHours += (decimal)c.chours;
    开发者_如何学Python                            break;
                            }
                        }
                    }
                }
            }
        }

---

Yes, no need for the foreach loops. Too much complexity, database hops and overall messiness.

Create an Entity Data Model in Visual Studio against your database, and EF will generate navigational properties based on the foreign keys you have setup in your database.

This will allow you join on the FK's properly using the Include statement.

---

You should definitely seek out either a LINQ to Entities replacement, or at least an Entity SQL replacement for this code.

Each time you specify a query using the EF ObjectContext, and then enumerate the query (e.g. by doing a foreach over it, or converting it to a list with ToList(), etc.), you'll be sending another query to the database. In your example, you have 3 nested foreach loops, so you're sending at least 3 distinct queries to the database (and maybe more depending on what child data you're pulling along with the main queries), when you might be able to only send a single query that contains the appropriate joins between your tables to get all the data in a single shot. Your method is also seemingly pulling back a lot more data than you probably need to, because you're retrieving all the records via the foreach loop, then effectively joining/filtering all the data in-memory in your code. Also, if you are using lazy-loading, you may be sending additional queries to fetch related data. You can eager fetch this data in the query using an Include(...) call, which might be faster depending on how the results are used.

If you implement a proper LINQ to Entities query, and don't enumerate it until the query is fully specified, you may end up with a much more performant and efficient query, as EF will look at everything your query is doing, and will generate the complete SQL statement for you. The query will then only be executed when you enumerate it at the end. This is the delayed execution bonus that you get automatically from LINQ.

Note that these are mostly generalizations, but the main point is that you should avoid pulling back large record sets from the database and filtering in memory, and you should avoid enumerating an EF query until you know that it's pretty finalized and ready to go.