LINQtoCRM

Querying CRM with Linq

Webcasts

• Getting Started
• Querying relationships, including many-to-many

Samples

Setup:

    CrmService service = new CrmService();
    service.Url = "http://192.168.150.2:5555/mscrmservices/2007/crmservice.asmx";
    service.Credentials = 
        new NetworkCredential("administrator", "pass@word1", "litwareinc");
    service.CrmAuthenticationTokenValue = token;

	ICrmService s = new CrmWebService(service);
	CrmQueryProvider p = new CrmQueryProvider(s);

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 };

Many-to-many join:

	var res = from u in p.Linq<systemuser>()
			  join sr in p.Linq<systemuserroles>() 
			  on u.systemuserid.Value equals sr.systemuserid.Value
			  join r in p.Linq<role>() 
			  on sr.roleid.Value equals r.roleid.Value
			  select new { u.fullname, r.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.contactid).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();