Introduction
The Linq to CRM project provides a custom query provider for
Microsoft Dynamics CRM. Besides updating this site, I'll report progress on my nascent blog:
http://www.itu.dk/~friism/blog/ Watch the
getting started tutorial (slightly outdated)
Found a query you think should work, but LinqtoCRM breaks or returns the wrong data? Please create a work-item or send it to me at friism+linqtocrm@gmail.com.
Features
- Where-clauses of some complexity (And/Or, greater/less-than etc.)
- Joins through relationtionships
- Translation of queries to FetchXML
- Parsing of result to anonymous types or CRM entities
- Orderby
- Skip/Take
- Count
- Contains, StartsWith, EndsWith
Samples
Setup:
ICrmService s = new CrmWebService(new CrmService());
s.Url = "http://10.0.0.3:5555/mscrmservices/2006/crmservice.asmx";
s.Credentials = new NetworkCredential("username", "password", "domain");
CrmQueryProvider p = new CrmQueryProvider(s);
p.Log = new XmlIndentingWriter(Console.Out);
Retrieve CRM entity with all attributes:
var res = from c in p.Linq<contact>()
select c;
Retrieve anonymous type:
var res = from c in p.Linq<contact>()
select new { c.fullname };
Joins:
var res = from c in p.Linq<contact>()
join a in p.Linq<account>()
on c.parentcustomerid.Value equals a.accountid.Value
select new { c.firstname, a.name };
Contains:
var res = from c in p.Linq<contact>()
where c.fullname.Contains("John")
select new { c.fullname };
Count:
int res = (from c in p.Linq<contact>()
select c).Count();
Chained queries and paging. This query retrieves the first 100 contacts in batches of 10:
var res = from c in p.Linq<contact>()
select new { c.fullname };
for (int i = 0; i < 10; i++)
{
var res2 = res.Skip(i*10).Take(10);
foreach (var v in res2)
WriteLine(v.fullname);
}
Distinct
var res = (from c in p.Linq<contact>()
orderby c.firstname
select new { c.firstname}
).Distinct();
Limitations
- You can only select anonymous types consisting of CRM entity attributes, like this: select new { myContact.fullname, myAccount.name }; or select full entities which will retrieve all attributes: from c in p.Linq<contact>() select c;
- No support for N:N relationships.
Status
- 22-8-2008: 0.2.7 Beta released
- 3-6-2008: 0.2.6 Beta released.
- 25-5-2008: 0.2.5 Beta released. All new features are by Mel Gerats.
- Unit tests
- Support for paging using Skip/Take
- Support for Contains, StartsWith and EndsWith
- Support for Count
- Support for chained queries
- Support for selecting crm entities
- Decoupling of LinqtoCRM and web service. LinqtoCRM can be compiled to a .dll and added to project
- 13-1-2008 : 0.2.4 Beta released.
- Bug fixes
- Unit test project (although no tests implemented)
- The project is fairly usable and is now in beta
- 13-1-2008 : 0.2.3 Alpha released.
- Bug fixes
- Now compiles out of the box -- just change web reference and you're golden.
- Minimal download with the files you need for your project
- Demo video
- 07-1-2008 : 0.2.2 Alpha released.
- Lots of smaller bug-fixes
- Changed from WCF to old-school service reference
- 26-12-2007 : 0.2.1 Alpha released.
- First new release in a while, sorry 'bout that!
- Has been compiled with VS 2008 RTM and runs against CRM 4.0 RC0 (latest VPC)
- Updates to result-processing code from Michael Höhne
- 06-08-2007 : 0.2.0 Alpha released.
- Based on insights from Matt Warren's series of articles
- Uses Visitor-pattern for query-generation
- Ordering implemented
- Ported to IQeryProvider/IQeryable-model
- 01-08-2007 : 0.1.1 Alpha released.
- Compiles and runs (in it's own fashion) under VS 2008 Beta 2
- FetchXML-library included as DLL in release (with thanks to Michael Höhne)
- 30-07-2007 : CodePlex site created, initial code uploaded (very crude early alpha - 0.1.0 Alpha)
Todo
- Handle arbitrary selectors. Matt Warren has shown how to do this but I'm having a very hard time grok'ing his implementation, even with the description on his blog. The basic idea is to look at the selector-expression and figure out where entity-attributes are actually referenced. These occurences are replaced with expressions that will evaluate correctly once the actual values have been retrieved. The code probably won't get as complex as Matt's because FetchXML is considerably less expressive than SQL and we can give up earlier.
- A visual debugger/visualizer to show were you've been too ambitious with your query. Bart De Smet seems to have the right idea
- A "pedal-to-the-metal" toggle. If the code will be running with acess to the SQL-server, the developer can specify that the query should be translated to SQL and run on the filtered views at the flick of a switch.
- The CRM web service is tangled up in the queryprovider code. It would be desirable if it could be moved outside and accessed though an interface. The code is rather dependent on the types exposed by the web service proxy-classes and it is not clear how this dependency could be removed without implementing something along the lines of SQLMetal/SPMetal.
Credits
