LINQ to Microsoft Dynamics CRM

Views (239)

Nishant Rana

11,325 Microsoft Employee

Like(0)

Report

Suppose this is our entity’s schema name “new_test”

and it contains following fields

new_name

new_lastname

Using CrmSvcUtil we have created the Entities classes and Data Context class.

https://nishantrana.wordpress.com/2010/08/11/using-crmsvcutil/

Now this is how we could use LINQ to query data using query expression as well as method based query.

Create the instace of DataContext class named xrm

var myXrm = new xrm(“CRMOnPremise”);

To loop through all the test records

foreach (var testRecord in myXrm.new_tests)
{
// print the information
}

To select a specific record

var singleRecord = (from myTest in myXrm.new_tests
where myTest.new_lastname == “Rana”
select myTest).Single();

var singleRecord1 = myXrm.new_tests
.Single(t => t.new_lastname == “Rana”);

To select all the records having last name as Rana

var allRecord = from myTest in myXrm.new_tests
where myTest.new_lastname == “Rana”
select myTest;

var allRecords1 = myXrm.new_tests.
Where(t => t.new_lastname == “Rana”);

To order the records

var allRecOrder=from myTest in myXrm.new_tests
orderby myTest.new_name ascending
where myTest.new_lastname==“Rana”
select myTest;

var allRecOrder1 = myXrm.new_tests
.OrderBy(t => t.new_name)
.Where(t => t.new_lastname == “Rana”);

To select specific field instead of the entire record

var singleField = from myTest in myXrm.new_tests
select myTest.new_name;

var singleField1 = myXrm.new_tests
.Select(t => t.new_name);

To return specific fields

var specificFields = from myTest in myXrm.new_tests
select new { myTest.createdby, myTest.createdon };

var specificFields1 = myXrm.new_tests
.Select(t => new { t.createdby, t.createdon });

Use of Take and Skip function
Take returns the given number of elements and ignores the rest
Skip skips the given number of elements and yielding the rest

var takeField = (from myTest in myXrm.new_tests
select myTest.new_name).Take(2);

var takeField1 = myXrm.new_tests
.Take(2)
.Select(t => t.new_name);

var skipField = (from myTest in myXrm.new_tests
select myTest.new_name).Skip(2);

var skipField1 = myXrm.new_tests
.Skip(2)
.Select(t => t.new_name);

Join – similar to inner join
The ‘select’ and ‘orderBy’ calls may only reference

a single common entity type.
We will get above error if we try to retrieve value from the other entity involved in join

var joinRecords = from t in myXrm.new_tests
join s in myXrm.systemusers on
t.ownerid.Value equals s.systemuserid
select new {t.new_name };

var joinRecords1=myXrm.new_tests
.Join(myXrm.systemusers,
t=>t.ownerid.Value,
s=>s.systemuserid ,
(t,s)=>new {t.new_name});

Where conditions with Contains,StartsWith, EndsWith and

Equal string functions

var test1 = from p in myXrm.new_tests
where p.new_name.Contains(“R”)
select p;
var test2 = from p in myXrm.new_tests
where p.new_name.StartsWith(“R”)
select p;
var test3= from p in myXrm.new_tests
where p.new_name.EndsWith(“D”)
select p;

var test11 = myXrm.new_tests
.Where(t => t.new_name.Contains(“R”));
var test22 = myXrm.new_tests
.Where(t => t.new_name.StartsWith(“R”));
var test32 = myXrm.new_tests
.Where(t => t.new_name.EndsWith(“D”));