LINQtoCRM

New Post: FetchXML vs RetrieveMultiple

NathanBaulch — Thu, 20 Nov 2008 22:17:49 GMT

I'm fairly new to MSCRM and love the idea of using Linq to query business entities!
The Linq queries I've written so far require about 50% less code than when I was using QueryExpression, which is great for readability/maintainability.

I was just curious, why has Fetch been used instead of RetrieveMultiple?
Are there things that can be done in FetchXML (via Stunnware's Fetch library) that can't be done using a regular CRM QueryExpression?
Is there any significant difference in performance between the two?

Cheers,
Nathan

Source code checked in, #25212

friism — Fri, 10 Oct 2008 10:56:09 GMT

Better error handling. One Unit tests fails and needs updating.

Source code checked in, #25186

friism — Thu, 09 Oct 2008 13:44:56 GMT

Experimental many-to-many support through CRMMetal.

Source code checked in, #25017

friism — Sat, 04 Oct 2008 07:44:21 GMT

Added exception-handling for Key comparisons.

Closed Issue: Query statecode

friism — Mon, 15 Sep 2008 08:06:43 GMT

To following query fails with a nullreferenceexception

var activeContacts = from c in provider.Linq<contact>()
where c.statecode.Value == ContactState.Active
select c;

Apparently c.statecode.Value (which is an enum ContactState) is translated to Convert(c.statecode.Value), which is not a memberexpression and thus line 302 (version 22501) fails
Comments: Added by steffana in 24289

Updated Wiki: Home

friism — Sun, 14 Sep 2008 19:34:08 GMT

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

Bug fixes by Mel Gerats

3-6-2008: 0.2.6 Beta released.

Support for Distinct

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.

Support for N:N could be a part of this effort. See discussion here: http://www.codeplex.com/LinqtoCRM/WorkItem/View.aspx?WorkItemId=4709

Credits

Mel Gerats for implementing paging, contains and loads of other stuff
Bart De Smet for the inspirational LINQtoSharePoint implementation
Michael Höhne for his FetchXML library
Matt Warren for his articles on QueryProvider-building: http://blogs.msdn.com/mattwar/archive/2007/08/03/linq-building-an-iqueryable-provider-part-v.aspx

Source code checked in, #24289

friism — Sun, 14 Sep 2008 19:29:38 GMT

Removed scc files

Source code checked in, #24287

friism — Sun, 14 Sep 2008 18:55:45 GMT

merged latest updates from staffana

Commented Issue: Where clauses on linked entity attributes fail

wesweeks — Fri, 12 Sep 2008 12:31:14 GMT

[Reported my mraufer]
This query fails:
var contacts = from c in provider.Linq<contact>()
join a in provider.Linq<account>()
on c.parentcustomerid.Value equals a.accountid.Value
where a.name.Contains("Sports")
select c;

The reason is that LinqtoCRM always creates filters on the root entity.
Comments: ** Comment from web user: wesweeks **

Need to amend this. the problem occurs is there is more the one criteria in the where clause. This fails as well:

var res = (from a in queryProvider.Linq<opportunity>()
join b in queryProvider.Linq<contact>()
on a.customerid.Value equals b.contactid.Value
where b.firstname== "name" &&
b.emailaddress1 == "my@email.com"
select a);

Commented Issue: Where clauses on linked entity attributes fail

wesweeks — Fri, 12 Sep 2008 12:21:04 GMT

Not working properly if where clause searches on data from both entities

var res = (from a in queryProvider.Linq<opportunity>()
join b in queryProvider.Linq<contact>()
on a.customerid.Value equals b.contactid.Value
where a.name== "name" &&
b.emailaddress1 == "my@email.com"
select a);
you get the error
\0x80041103 'Opportunity' entity doesn't contain attribute with Name = 'emailaddress1'.

The fetch XMl looks like this:
<fetch mapping="logical">
<entity name="opportunity">
<all-attributes />
<filter>
<condition attribute="name" operator="eq" value="name" />
<condition attribute="emailaddress1" operator="eq" value="my@email.com" />
</filter>
<link-entity name="contact" from="contactid" to="customerid" />
</entity>
</fetch>

So it goes back to the root entity only if the where specifies search critieria from both. I can specify for one entity or the other and it will work but would be very helpful to be able to do both.

Commented Issue: Query statecode

steffana — Tue, 02 Sep 2008 09:14:09 GMT

I fixed this issue by changing line 302 (v.22501) to:

MemberExpression mexp = ((b.Left is UnaryExpression) ? (b.Left as UnaryExpression).Operand : b.Left) as MemberExpression;

such that is uses the operand as attritute is Left expression is unary (e.g. Convert)

Commented Issue: Where clause with dates

steffana — Tue, 02 Sep 2008 09:04:12 GMT

I needed to write a query like

var query = from c in provider.Linq<contact>()
where c.createdon.UserTime <= DateTime.Now
select new { c.fullname }

Using "where c.createdon.Value <= DateTime.ToString("s")" does not work as less or equal is not a valid string operator.
I solved the problem by implementing a small fix in SetConditionValue in QueryFormatter.cs

private void SetConditionalValues(FetchCondition cond, BinaryExpression b)
{
MemberExpression mexp = b.Left as MemberExpression;
MemberExpression pmexp = mexp.Expression as MemberExpression;

// Changes this if, to go a level deeper anytime the member AND the parent member is a property (e.g. c.status.Value or c.createdon.UserTime)
// Previous version only did this for Value-property
if (mexp.Member.MemberType.Equals(MemberTypes.Property) && pmexp != null && pmexp.Member.MemberType.Equals(MemberTypes.Property))
{
// need to go one level deeper
cond.Attribute = (mexp.Expression as MemberExpression).Member.Name;
}
else
{
cond.Attribute = mexp.Member.Name;
}

object value = Expression.Lambda(b.Right).Compile().DynamicInvoke();
// check for null comparison
if (value == null)
{
if (cond.ConditionOperator == FetchConditionOperator.Equal)
cond.ConditionOperator = FetchConditionOperator.Null;
else if (cond.ConditionOperator == FetchConditionOperator.NotEqual)
cond.ConditionOperator = FetchConditionOperator.NotNull;
else
throw new Exception("Strange null-comparison");
}
else
{
if (value is DateTime)
{
// If the value to compare to is of type DateTime, format it in a way fetchXml will accept
cond.Values.Add(((DateTime)value).ToString("s"));
}
else
{
cond.Values.Add(value);
}
}
}
Comments: ** Comment from web user: steffana **

In order not to mess up where clauses on linked entites, the following line:
if (mexp.Member.MemberType.Equals(MemberTypes.Property) && pmexp != null && pmexp.Member.MemberType.Equals(MemberTypes.Property))

should be extended to:
if (mexp.Member.MemberType.Equals(MemberTypes.Property) && pmexp != null && pmexp.Member.MemberType.Equals(MemberTypes.Property) && pmexp.Type.BaseType.Name != "BusinessEntity")

Created Issue: Query statecode

steffana — Wed, 27 Aug 2008 12:06:53 GMT

Created Issue: Where clause with dates

steffana — Mon, 25 Aug 2008 12:03:45 GMT

Updated Wiki: Home

friism — Fri, 22 Aug 2008 10:28:33 GMT

Introduction

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

Bug fixes

3-6-2008: 0.2.6 Beta released.

Support for Distinct

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.

Support for N:N could be a part of this effort. See discussion here: http://www.codeplex.com/LinqtoCRM/WorkItem/View.aspx?WorkItemId=4709

Credits

Mel Gerats for implementing paging, contains and loads of other stuff
Bart De Smet for the inspirational LINQtoSharePoint implementation
Michael Höhne for his FetchXML library
Matt Warren for his articles on QueryProvider-building: http://blogs.msdn.com/mattwar/archive/2007/08/03/linq-building-an-iqueryable-provider-part-v.aspx

Released: 0.2.7 Beta (Aug 22, 2008)

Fri, 22 Aug 2008 10:24:44 GMT

From readme

Welcome to LinqtoCRM
 
Getting Started
===============
 
* Add the following files to your project: CrmQueryProvider.cs, CrmWebService.cs, Evaluator.cs, 
	ExpressionVisitor.cs, IService.cs, QueryFormatter.cs, QueryProvider.cs, TypeSystem.cs
* Correct the name of the webservice reference in CrmWebService.cs
* Add a reference to stunnware.CRM.Fetch.dll.
* Add "using LinqtoCRM;"
 
Start Coding!
 
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.
 
Notes
=====
The current project was built with VS 2008 RTM and is known to work against CRM 4.0 RTM.
 
Check the codeplex page for updates: http://www.codeplex.com/LinqtoCRM
 
Found a query you think should work, but LinqtoCRM breaks or returns the wrong data? Please 
send it to me at friism+linqtocrm@gmail.com or create a workitem in the codeplex site.
 
Contributers
============
 
* Michael Friis (initial version, maintenance)
* Michael Höhne (fixes to result parsing code, provided FetchXML lib)
* Mel Gerats (implemented paging with skip/take, chained queries, contains, count and many other improvements to the code)
 
---
 
Michael Friis
http://www.itu.dk/~friism/blog/

Updated Release: 0.2.7 Beta (Aug 22, 2008)

Fri, 22 Aug 2008 10:24:44 GMT

From readme

Welcome to LinqtoCRM
 
Getting Started
===============
 
* Add the following files to your project: CrmQueryProvider.cs, CrmWebService.cs, Evaluator.cs, 
	ExpressionVisitor.cs, IService.cs, QueryFormatter.cs, QueryProvider.cs, TypeSystem.cs
* Correct the name of the webservice reference in CrmWebService.cs
* Add a reference to stunnware.CRM.Fetch.dll.
* Add "using LinqtoCRM;"
 
Start Coding!
 
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.
 
Notes
=====
The current project was built with VS 2008 RTM and is known to work against CRM 4.0 RTM.
 
Check the codeplex page for updates: http://www.codeplex.com/LinqtoCRM
 
Found a query you think should work, but LinqtoCRM breaks or returns the wrong data? Please 
send it to me at friism+linqtocrm@gmail.com or create a workitem in the codeplex site.
 
Contributers
============
 
* Michael Friis (initial version, maintenance)
* Michael Höhne (fixes to result parsing code, provided FetchXML lib)
* Mel Gerats (implemented paging with skip/take, chained queries, contains, count and many other improvements to the code)
 
---
 
Michael Friis
http://www.itu.dk/~friism/blog/

Created Issue: Ditch FetchXMLlib in favour of LinqtoXSD

friism — Fri, 22 Aug 2008 10:14:25 GMT

http://live.mscommunity.net/blogs/dadamec/archive/2008/02/29/linq-to-xsd-alpha-0-2-refresh-for-visual-studio-2008.aspx

Closed Issue: Where clauses on linked entity attributes fail

melg — Fri, 22 Aug 2008 07:57:47 GMT

Source code checked in, #22501

melg — Fri, 22 Aug 2008 07:57:43 GMT

Filters on linked entities are now applied to the correct entity