Entity Framework 4.0 and the AJAX Autocomplete Extender.

Shows how to use AutocompleteExtender to populate subjects and databind quotations via Entity Framework 4.0

Now that as of v4.0 of Entity Framework things have matured nicely, I feel more comfortable working with the product. This article will focus on a database of famous quotations that I"ve pared down to a downloadable size for a demo, and how to wire up the AJAX Toolkit autocomplete extender to a textbox that is used to typeahead a Subject. When the ClientItemSelected event fires, the Display button Click is invoked, and this causes an EF query to display the matching quotes in a DataList.

The database has three tables: Author, Subject, and Quote. Author and Subject both have foreign key relations to the corresponding AuthorId and QuotationId columns in the Quotes table, which creates Navigation Properties in the Entity that allow it to pull the objects from the other tables into the results of a query. This makes it super - easy to construct LINQ queries that can return quotations by Subject, and have the Subject and Author properties autopopulated into the resulting Quotes. Of course as you can see below, the Subject object also has a Quotes Navigation property, and the Author object has a Quotes Navigation property. This means we could just as easily retrieve quotes by Subject, or quotes by Author.

Here is a view of the EF designer after dragging the tables from the server connection:



Querying this for some quotes is extremely simple:

string subject = this.txtAutoComplete.Text;
using (var context = new QUOTES2Entities())
{
var items = from q in context.Quotes
where q.Subject.SubjectText ==subject
select q;
List<QuotesAppWeb.Quote> stuff = items.ToList();
this.DataList1.DataSource = stuff;
DataList1.DataBind();
}

The Autocomplete extender markup looks like the following:

<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>

<asp:TextBox ID="txtAutoComplete" runat="server" Width="252px">
</asp:TextBox>
<asp:AutoCompleteExtender runat="server"
ID="AutoComplete1"
BehaviorID="autoComplete"
TargetControlID="txtAutoComplete"
ServicePath="Default.aspx"
ServiceMethod="GetCompletionList"
MinimumPrefixLength="2"
CompletionInterval="10"
EnableCaching="true"
CompletionSetCount="100"
OnClientHiding="OnClientCompleted"
OnClientPopulated="OnClientCompleted"
OnClientPopulating="OnClientPopulating"
OnClientItemSelected ="OnClientItemSelected"
>

We attach the extender to our TextBox via the TargetControlID property. We tell the extender what ServicePath and Method to run via the ServicePath and ServiceMethod properties. Note that this does not have to be a webservice at all. In fact, the code I am running here is right in the page, like this:

[System.Web.Services.WebMethod]
[System.Web.Script.Services.ScriptMethod]
public static List<string> GetCompletionList(string prefixText, int count)
{

using (var context = new QUOTES2Entities())
{

var subjects = from s in context.Subjects
where s.SubjectText.StartsWith(prefixText)
select s;
List<String> cList = new List<String>();
foreach (var subject in subjects)
{
cList.Add(subject.SubjectText);
}
return cList;
}
}

The other client events are used to display an animated "loading" gif inside the textbox. Here are the OnClientPopulating, OnClientCompleted and OnClientItemSelected client script methods:

<script type="text/javascript">

function OnClientPopulating(sender, e) {
sender._element.className = "loading";
}
function OnClientCompleted(sender, e) {
sender._element.className = "";
}
function OnClientItemSelected(source, eventArgs) {

$get('<%=this.txtAutoComplete.ClientID%>').value = eventArgs.get_value();
$get('<%=this.buttonDisplay.ClientID%>').click();
}
</script>

What these do is set the style for the image background on the textbox, and on Completed, remove the style so the loading image goes away. Finally the OnClientItemSelected uses standard jQuery constructs to get the text of the subject the user selected, and fire the Click event on the Display button.

And here is an example of the display when an item has been selected:



You can download the Visual Studio 2010 sample solution here (approx 2MB). Please note that you'll need to create a new SQL Server database "MINIQUOTES" and then run the provided SQL Script against it to create the schema and populate the tables. If you are using SQLExpress or any SQL Server installation that doesn't understand "(local)" in the connection string, you will need to modify the Entity connection string in web.config. Also, in this demo I used the 4.0 binary version of the AJAX toolkit.