What are the best practices in creating a data access layer for an object that has a reference to another object?

Question

(sorry for my English) For example, in my DAL,I have an AuthorDB object, that has a Name and a BookDB object, that has a Title and an IdAuthor.

Now, if I want to show all the books with their corresponding author's name, I have to get a collection of all the Books, and for each of them, with the IdAuthor attribute, find the Author's name. This makes a lot of queries to the database, and obviously, a simple JOIN could be used.

What are my options? Creating a 'custom' object that contains the author's name and the title of the book? If so, the maintenance could become awful.

So, what are the options? Thank you!

Answer 1

You can create a View in the database that has the join built into it and bind an an object to that, e.g. AuthorBooksDB. It doesn't create too bad a maintenance headache since the view can hide any underlying changes and remains static.

Answer 2

If you can separate the database query from the object building, then you could create a query to get the data you need. Then pass that data to your builder and let it return your books.

With Linq to SQL, that can be done as easily as:

public IEnumerable<Book> AllBooks()
{
    return from book in db.Books
      join author in db.Authors on book.AuthorId equals author.Id
      select new Book() 
         { 
          Title = book.Title, 
          Author = author.Name,
         };
}

The same can be achieved with DataTables / DataSets:

public IEnumerable<Book> AllBooks()
{
    DataTable booksAndAuthors = QueryAllBooksAndAuthors(); // encapsulates the sql query

    foreach (DataRow row in booksAndAuthors.Rows)
    {
     Book book = new Book();
     book.Title = row["Title"];
     book.Author = row["AuthorName"];
     yield return book;
    }
}

Answer 3

Thank you very much for your inputs.

Actually, we are trying to keep the database objects as close as possible to the actual columns of the corresponding table in the database. That's why we cannot really add a (string) 'Author' attribute to the BookDB object.

Here is the problem I see with using 'View' objects. In the database, if the schema has to be modified for any reason (e.g: In the Book table, the 'Title' column has to be modified for 'The_Title', how do we easily know all the 'View' objects that have to be modified? In other words, how to I know what objects have to be modified when they make queries that use multiple joins?

Here, since we have a AuthorsBooks object, we can see by the name that it probably makes a query to the book and author tables. However, with objects that make 4 or 5 joins between tables we cannot rely on the object name.

Any ideas? (Thank you again, this is a great site!)

Answer 4

Don't write something buggy, inefficient, and specialized ... when reliable, efficient, and generic tools are available. For free.

Pick an ORM. NHibernate, ActiveRecord, SubSonic, NPersist, LinqToEF, LinqToSQL, LLBLGenPro, DB4O, CSLA, etc.

Answer 5

I suggest you take a look at Domain Driven Design. In DDD, you get all business objects from a repository. The repository hides your data store and implementation, and will solve your problems on how to query data and keeping track of database changes. Because every business object is retrieved from the repository, the repository will be your single point of change. The repository can then query your database in any way you find efficient, and then build your domain objects from that data:

var books = new BookRepository().GetAllBooks();

You should be able to code the repositories with any of the technologies mentioned by Justice.