0

I have a couple of DBML generated classes which are linked together by an id, e.g.

ClassA {
    AID,
    XID,
    Name
}

ClassB {
    AID,
    ExtraInfo,
    ExtraInfo2
}

In using something like db.ClassAs.Where(XID == x) and iterating through that result, it ends up executing a query for each of the ClassAs and each of ClassBs, which is slow.

Alternatively, I've tried to use ExecuteQuery to fetch all the info I care about and have that return a ClassA. In iterating over that I end up with it doing the same, i.e. doing alot of individual fetches vs. just 1. If I store it in a ClassC (that is not associated with a DB entity) which has the fields of interest of both ClassA and ClassB, this query is much faster, but it's annoying b/c I just created IMO an unnecessary ClassC.

How can I still use ClassA, which associates to ClassB, and still use ExecuteQuery to run 1 query vs. A*B number of queries?

tshepang
  • 12,111
  • 21
  • 91
  • 136
Faury
  • 1

1 Answers1

0

If you have associations you shouldn't need to use ExecuteQuery().

Here's an example using some imaginary Book Library context and anonymous types for the result:

var results = 
    Books
    .Where(book => book.BookId == 1)
    .Select(book =>
        new
        {
            book.Name,
            AuthorName = book.Author.Name,  //Is a field in an associated table.
            book.Publisher, //Is an associtated table.
        });

EDIT: without anon types

var results = 
        Books
        .Where(book => book.BookId == 1)
        .Select(book =>
            new BookResult()
            {
                BookName = book.Name,
                AuthorName = book.Author.Name,  //Is a field in an associated table.
                Publisher = book.Publisher, //Is an associtated table.
            });
DaveShaw
  • 52,123
  • 16
  • 112
  • 141
  • I don't want an anon type. In your example, I would like to get a Book type because I would like to the feed the Book type into a JavaScriptSerializer that has a JavaScriptConverter specifically for a the Book type vs. I would not be able to use the converter with an anon type. Ideally , I would care to end up with a Book instance and I would only have fetched the book name, the name of the Author, and book publisher in one query instead of doing two queries, i.e. SELECT * FROM Book Where ... and SELECT * FROM Author Where ... Consider: var results = ... foreach (Book x in results) {} – Faury Jul 07 '10 at 19:11
  • In using an anon type, the foreach complains, which makes sense, but if I change var results = ... to IEnumerable results = ..., in iterating through the for loop, it ends up executing SELECT * FROM Books Where ... and SELECT * FROM Authors Where ..., which is what I'm trying to prevent vs. it executing 1 more optimal query. – Faury Jul 07 '10 at 19:12
  • Instead of an anon type, can you have one that stores you results... Updating the answer now. – DaveShaw Jul 07 '10 at 19:37