CODEWITHSUNDEEP
c#asp.net-mvclinq-to-sqlmodel-view-controller
Jeff Widmer
Jeff Widmer

Reputation: 4876

How to make only single database query when accessing Linq to SQL IEnumerable object?

My application constructs a Linq To Sql query and then populates a model which is then passed to the view. The view then accesses the IEnumerable object (from the Linq to Sql query) to display the user interface. The problem I am having is that each time the View accesses the IEnumerable object from the model, the linq to sql database query is made. I want the database to be queried once.

My controller looks like this:

    IQueryable<Item> items = (from item in db.Items
                             orderby item.Name ascending
                             select item);
    ItemModel m = new ItemModel(items);

    return View("Index", m);

Where ItemModel is defined like this: 

public class ItemModel
{

    public ItemModel(IEnumerable<Item> items)
    {
        this.Items = items;
    }

    public IEnumerable<Item> Items { get; private set; }

}

My View then does something like this 

Item Count: <%= Html.Encode(Model.Items.Count())%>
<% foreach (var item in Model.Items) { %>
    <% Html.RenderPartial("ItemControl", item); %>
<% } %>

Whenever Model.Items is accessed in the view, I am seeing a database query. In the example above I would see two database queries for the two Model.Items calls (in the actual view Model.Items is accessed more than twice so the performance hit is much worse).

How should I restructure my view or model or controller to make only a single query to the database?

Upvotes: 1

Views: 569

Answers (1)

Frank Krueger
Frank Krueger

Reputation: 71033

Change:

ItemModel m = new ItemModel(items);

To:

ItemModel m = new ItemModel(items.ToArray());

The idea is to perform the query and store the results in an array so that accessing those results multiple times only incurs 1 DB hit.

Upvotes: 3

Related Questions