I have been spending a decent amount of time reading up and playing around with the new features that are coming with Visual Studio 2008. One site that I regularly read is Scott Guthrie's blog. Lately he has been posting a lot about LINQ and I really like the idea behind the technology. Having a more object oriented way of querying opens up a new realm of possibilities as well as makes life easier when writing code. Intellisense is a wonderful thing!
Today, I'm going to be writing about using LINQ in combination with CRM's filtered views to create a very basic web portal that allows you to search for Accounts and display the name and primary Contact in a GridView. I'll demostrate how simple it is to create the LINQ objects and hook into CRM's database all while being 100% supported and secure. For those not familiar with CRM filtered views, they are basically a Microsoft supported way to query the database directly and still maintain security on the data. So let's get started.
I started out by firing up the latest build of VS 2008 (Beta 2) and created a new web project on my pre-built CRM VPC. After creating the project, I right clicked on the App_Code folder and chose to add a new item. In the New Item list, displayed below, I chose LINQ to SQL Classes file and named it CRM.dbml.
After adding the new file I then went into the Server Explorer and connected to my Sql Server's MSCRM database. I then opened up my CRM.dbml file and dragged the FilteredAccount and FilteredContact views from the Server Explorer.
*Notice that after you drag the views into the main window, they appear as a package with properties on them. You can select each property (field) individually and set properties on them. To increase the efficiency of the querying itself, fields such as "Delay Loaded" will only load the data for that field if it's used. For more details on specifying properties check out Scott Guthrie's post here.
After adding the 2 filtered views, I added a TextBox, Button, and GridView Asp.Net controls to my Default.aspx.
*Another very cool feature coming with VS2008 is the split view display. This display allows you to view the output page as you write the Html. I find it handy for CSS updates and manipulating the page to get that exact look and feel.
I then opened the code behind and started writing my code on my "Find" button's onclick event. By default the namespace System.Linq is added in my header and the intellisense immediately picked up.
protected void queryButton_Click(object sender, EventArgs e)
{
if (search.Text != string.Empty && search.Text.Length > 0)
{
CRMDataContext db = new CRMDataContext();
accountGrid.DataSource = from a in db.FilteredAccounts
join con in db.FilteredContacts
on a.primarycontactid equals con.contactid
where a.name.StartsWith(search.Text)
select new
{
Name = a.name,
PrimaryContact = con.fullname
};
accountGrid.DataBind();
}
}
The first line I added is my data context. To reference my CRM filtered views, I simply type CRMDataContext. I then set my DataGrid's (Id = accountGrid) DataSource to the LINQ query I am going to run against the object. Notice how similar the query looks like Sql. When creating the query, I joined my FilteredAccount to the FilteredContact to return the Primary Contact's fullname. While typing the query the intellisense made it very easy to use. I didn't have to go back to my view and see what the fields name were, they were right there. One less Alt+Tab is a big plus in my book! You can see it in the screenshot below.
After finishing my code, I hit save and ran my project. Below are the results.
Here is the CRM view of the data.
So there you have it. CRM data displayed in a web portal, all supported and secure, using LINQ with VS 2008 and CRM's filtered views.
Update: Michael Friis emailed me to let me know of a project he is currently working on relating to LINQ to CRM. I suggest checking it out here
Over the next few weeks I plan to start posting all about my experiences with Microsoft CRM. I plan to do a few write-ups on using some of the new features coming with Visual Studio 2008 "Orcas" with CRM 3.0. I might also delve into using Sharepoint 2007 (MOSS) and Microsoft Windows Workflow Foundations but we will see.
If you have any requests on things you would like to see, please feel free to leave me a comment here and I will do the best I can to fit it in, or at least talk about it :-).