Archives for category: LINQ

Let’s face it.  There are times when a stored procedure is going to create a more efficient query, and provide the flexibility you need to return attributes from multiple SQL tables in a non-traditional way.

In my case, I had a number of different log tables that I wanted to combine into a single result.  My log tables contained different field attributes, and so I was not able to simply use the ObjectQuery<T>.Union method to combine my results.  It was much easier to simply create a stored procedure that could translate the information into a nice generic result from the combined tables.

It was this result that I wanted to work with, not the individual tables.  You can accomplish this in three easy steps:

1) Update your Model diagram (edmx) with the stored procedure you want to add.  (Update Model from Database –> Add –> Stored Procedure)

step1

2) Right-click on your diagram and choose Add –> Function Import.  Enter the name you want to use as the Entity function to retrieve the stored procedure.  Select the stored procedure you want to associate with this Entity Function.  Press Get Column Information.  Then select Create New Complex Type.  Enter the name of the custom Complex Type Entity you want to associate with the stored procedure.

step2

3) That’s it!  Now you can use the function in a very elegant way and still have a strongly typed object to work with.

step3

Advertisements

I have an auto-generated entity framework Entity Data Model Designer File (edmx).  And while I am using Data Annotations for validation purposes, there are times when retrieving the maxlength of an entity property programmatically would be very useful.

image

In some cases, I may want to simply truncate the value, rather than throw an error if the maxlength of a property is exceeded.  Or I may want to surface the maxlength property to the client for client-side validation purposes.

It turns out to be a simple query, but it took me a while to find it.  You will need to reference the following libraries:

using System.Data.Objects.DataClasses;
using System.Data.Metadata.Edm;

This is the method to retrieve the MaxLength property.  Please note that you may get an exception if the property does not have a MaxLength property specified.

public static int? GetMaxLength(this EntityObject entityObject, string entityProperty)
{
CATDBEntities _context = new CATDBEntities();
int? result = null;
using (_context)
{
var q = from meta in _context.MetadataWorkspace.GetItems(DataSpace.CSpace)
.Where(m => m.BuiltInTypeKind == BuiltInTypeKind.EntityType)
from p in (meta as EntityType).Properties
.Where(p => p.DeclaringType.Name == entityObject.GetType().Name
&& p.Name == entityProperty
&& p.TypeUsage.EdmType.Name == “String”)
select p;

    var queryResult = from meta in _context.MetadataWorkspace.GetItems(DataSpace.CSpace)
.Where(m => m.BuiltInTypeKind == BuiltInTypeKind.EntityType)
from p in (meta as EntityType).Properties
.Where(p => p.DeclaringType.Name == entityObject.GetType().Name
&& p.Name == entityProperty
&& p.TypeUsage.EdmType.Name == “String”)
select p.TypeUsage.Facets[“MaxLength”].Value;
if (queryResult.Count() > 0)
{
result = Convert.ToInt32(queryResult.First());
}
}
return result;
}

To call this method, simply instantiate the entity object and pass in the proper parameters:

project _project = new project();
int? maxLength = DataAnnotation.GetMaxLength(_project, “project_name”);

I’ll be honest. I don’t like writing CAML queries. There, I said it…

How about using LINQ instead to access the data in your lists and libraries? It’s really pretty simple.

You can use the SPMetal.exe located in c:\program files\common files\microsoft shared\web server extensions\15\bin to generate both a data context and LINQ classes which you can consume in your SharePoint project. One you do that, you no longer have to write giant ugly text string queries, but can instead focus on managing your SharePoint data with strongly-typed, entity classes.

To use SPMetal to generate the class file you will need to consume into your SharePoint 2013 project, create a batch file (*.bat) in your development environment as follows:

c:\program files\common files\microsoft shared\web server extensions\15\bin\spmetal.exe /web:http://dev.mydevweb.com
/code:”C:\Users\rhutton\Desktop\SPMetal.cs”
pause

When you run your batch file, a class file will be created. If you specified the class file to use the (*.cs) extension, then the data context and classes will be generated using C#. However, if you specify the class file to use a (*.vb) extension, then the data context and classes will be generated using that other language. 😉

SPMetal creates a data context and classes for each of your lists and libraries so you can access them in a strongly-typed, object oriented way.

Capture2.PNG

Now writing queries is super easy, readable, and definitely more maintainable:

Capture.PNG

One of the beautiful things about using LINQ to SharePoint to retrieve data from your lists and libraries is the absolute ease in which you can retrieve data from two or more lists which are joined by lookup fields.

For more information on setting up LINQ to SharePoint in your developmennt environment, check out my blog entitled Using SPMetal to Generate SharePoint 2013 LINQ Classes.

For example, I have two custom lists: Client (which contains ClientName, ClientLogo) and ClientQuote (which contains a Client lookup field, as well as Quote, ContactName, and ContactTitle).

With LINQ to SharePoint, I can easily write a query to retrieve information from both lists and populate a custom object called ClientQuotes:

If I were to use this in a custom web part, I would get the following error: The query uses unsupported elements, such as references to more than one list, or the projection of a complete entity by using EntityRef/EntitySet.

This error can be resolved by one simple adjustment:

What this does is force immediate query evaluation and breaks the query into two steps.

I am in the processing of learning how to use SPMetal to replace those dreaded SPQuery CAML queries. So far, very nice!

If you need more information on using SPMetal to add LINQ to SharePoint to your SharePoint development environment, check out my blog entitled Using SPMetal to Generate SharePoint 2013 LINQ Classes. (This is also relevant to SharePoint 2010)

One scenario I ran into was needing to filter my results on a multi-value lookup column.

In this example, I have two custom lists: Clients and Services. A client may be associated with one or more services, so I created a multi-value lookup column on the Clients list. Now I want to retrieve all Clients which are associated with a Service entitled “Applications”.

One thing I need to do in my LINQ query is be sure to for immediate query evaluation using a .ToList() statement. This will prevent me from getting an “Unsupported Elements, References to More than One List” error. For more information on this, please check out my blog entitled SPMetal List Joins “Unsupported Elements, References to More than One List” Error.

One thing to note is that I am using a data service and returning strongly-typed objects. An alternative to this would be to simply use anonymous types if you wanted to process the results immediately, rather than return it from a method.

publicclassClients

{

public string Title { get; set; }

public string Logo { get; set; }

}

If the List field was simply a lookup field, then filtering with LINQ is very easy and would look something like this:

public IEnumerable<Clients> RetrieveClients()

{

SPMetalDataContext dataContext = new SPMetalDataContext(SPContext.Current.Web.Url);

var query = from client in dataContext.CatapultClients.ToList()

where client.Services.Title = “Applications”

orderby client.Order, client.Title

selectnewClients

{

Title = client.Title,

Logo = client.ClientLogo

};

return query;

}

However, since it is a multi-value lookup field, the filter expects an actual Service object. First I retrieve the Service, and then I pass it in as a filter.

public IEnumerable<Clients> RetrieveClients()

{

SPMetalDataContext dataContext = new SPMetalDataContext(SPContext.Current.Web.Url);

var myService = dataContext.CatapultServices.Where(e => e.Title == “Applications”).ToList().Single();

var query = from client in dataContext.CatapultClients.ToList()

where client.Services.Contains(myService)

orderby client.Order, client.Title

selectnewClients

{

Title = client.Title,

Logo = client.ClientLogo

};

return query;

}

I love using the XrmServiceContext Caching issue in order to use LINQ queries to retrieve items from our CRM implementation. So much nicer to work with than the old SOAP model!!!

However, I ran into an issue pretty quickly with a custom service I created that provisions a SharePoint team site for a CRM opportunity. I was getting intermittent caching errors. The script’s attempt to retrieve the opportunity record from CRM would result in the following error:

Sequence contains no elements

I researched the issue and was unable to find a way to configure caching and even attempted to explicitly remove items from the cache, but the only thing that worked for me was to dispose and recreate the XrmServiceContext object as follows:

private void RefreshContext() {
if (context != null) {
context.Dispose();
}

ClientCredentials credentials = new ClientCredentials();
credentials.UserName.UserName = MscrmWebService.LoginName;
credentials.UserName.Password = MscrmWebService.Password;
string link = MscrmWebService.Link;
Uri organizationUri = new Uri(MscrmWebService.Link);
Uri homeRealmUri = null;

using (serviceProxy = new OrganizationServiceProxy(organizationUri, homeRealmUri, credentials, null)) {
serviceProxy.ServiceConfiguration.CurrentServiceEndpoint.Behaviors.Add(new ProxyTypesBehavior());
service = (IOrganizationService)serviceProxy;
context = new XrmServiceContext(service);
}
}

public Opportunity RetrieveOpportunity(Guid opportunityId) {
RefreshContext();
return context.OpportunitySet.Where(e => e.OpportunityId == opportunityId).Single();
}

For those of us who have had the unfortunate experience of trying to access CRM data using SOAP queries, it is a luxury to be able to query CRM entities using LINQ.

It is pretty easy to setup your .NET project to use CRM and LINQ. Simply install the CRM SDK. This link is to the CRM 2013 SDK, but you can certainly do this in CRM 2011 as well:
http://www.microsoft.com/en-us/download/details.aspx?id=40321

Once you have extracted the CRM SDK, you can use the CrmSvcUtil command line function to generate an xrm.cs file to include in your project, which generates the CRM entity definitions.
http://msdn.microsoft.com/en-us/library/ff681563.aspx

  1. Open the Command Prompt as an Administrator.
  2. Change directories to the SDK directory which contains the CrmSvcUtil executable.

cd c:\CRM-2013-SDK\SDK\Bin

  1. Type the CrmSvcUtil.exe command with the following parameters:
    1. /out – [name of the file you want generated]
    2. /url – [link to the organization service of your CRM implementation]
    3. /domain – [domain name of the CRM administrator account you will use to generate the file]
    4. /username – [user name of the CRM administrator account you will use to generate the file]
    5. /password – [password of the CRM administrator account you will use to generate the file]

CrmSvcUtil.exe /codeCustomization:”Microsoft.Xrm.Client.CodeGeneration.CodeCustomization, Microsoft.Xrm.Client.CodeGeneration” /out:Xrm.cs /url:https://crm.mycrmserver.com/myorganization/XRMServices/2011/Organization.svc /domain:mydomain /username:mycrmadministrator /password:mycrmadminpassword /namespace:Xrm /serviceContextName:XrmServiceContext

Once the xrm.cs file has been generated, you can include it as a class in your .Net project. Then you can create a context object:

private void RefreshContext()

{

if (context != null)

{

context.Dispose();

}

ClientCredentials credentials = new ClientCredentials();

credentials.UserName.UserName = MscrmWebService.LoginName;

credentials.UserName.Password = MscrmWebService.Password;

string link = isDev ? MscrmWebService.LinkDev : MscrmWebService.Link;

Uri organizationUri = new Uri(MscrmWebService.Link);

Uri homeRealmUri = null;

using (serviceProxy = new OrganizationServiceProxy(organizationUri, homeRealmUri, credentials, null))

{

serviceProxy.ServiceConfiguration.CurrentServiceEndpoint.Behaviors.Add(new ProxyTypesBehavior());

service = (IOrganizationService)serviceProxy;

context = new XrmServiceContext(service);

}

}

And from there you can query the CRM entities like you would any other LINQ entity. Some examples include:

Retrieve CRM Contact by Id

public Contact RetrieveContact(Guid contactId)

{

RefreshContext();

return context.ContactSet.Where(e => e.ContactId == contactId).Single();

}

 

Retrieve CRM Contact by Name

public Contact RetrieveContact(string firstName, string lastName)

{

RefreshContext();

return context.ContactSet.Where(e => e.FirstName == firstName && e.LastName == lastName).Single();

}

 

Retrieve CRM Account by Id

public Account RetrieveAccount(Guid accountId)

{

RefreshContext();

return context.AccountSet.Where(e => e.AccountId == accountId).Single();

}

Retrieve CRM Active Accounts

public IQueryable<Account> RetrieveActiveAccounts()

{

return

context.AccountSet.Where(

e =>

(e.CustomerTypeCode.Value == (int)ContactType.Customer ||

e.CustomerTypeCode.Value == (int)ContactType.Prospect) && e.StateCode == (int)StateCode.Active)

.OrderBy(e => e.Name);

}

Retrieve CRM Accounts Last Modified

public List<Account> RetrieveLastModifiedAccounts(int recordLastModifiedDays, bool activeOnly, ref bool isError, ref string errorDetails)

{

System.DateTime today = System.DateTime.Today;

if (recordLastModifiedDays > 0)

{

today = today.AddDays(recordLastModifiedDays * -1);

}

if (activeOnly)

{

return context.AccountSet.Where(e => e.StateCode == (int)StateCode.Active && e.ModifiedOn.Value >= new DateTime(today.Year, today.Month, today.Day)).OrderBy(e => e.Name).ToList();

}

return context.AccountSet.Where(e => e.ModifiedOn.Value >= new DateTime(today.Year, today.Month, today.Day)).OrderBy(e => e.Name).ToList();

}

 

Retrieve CRM Contacts Last Modified

public List<Contact> RetrieveLastModifiedContacts(int recordLastModifiedDays, bool activeOnly, ref bool isError, ref string errorDetails)

{

System.DateTime today = System.DateTime.Today;

if (recordLastModifiedDays > 0)

{

today = today.AddDays(recordLastModifiedDays * -1);

}

if (activeOnly)

{

return context.ContactSet.Where(e => e.StateCode == (int)StateCode.Active && e.ModifiedOn.Value >= new DateTime(today.Year, today.Month, today.Day)).OrderBy(e => e.FullName).ToList();

}

return context.ContactSet.Where(e => e.ModifiedOn.Value >= new DateTime(today.Year, today.Month, today.Day)).OrderBy(e => e.FullName).ToList();

}

 

Retrieve CRM Opportunities Last Modified

public List<Opportunity> RetrieveLastModifiedOpportunities(int recordLastModifiedDays, bool activeOnly, ref bool isError, ref string errorDetails)

{

System.DateTime today = System.DateTime.Today;

if (recordLastModifiedDays > 0)

{

today = today.AddDays(recordLastModifiedDays * -1);

}

if (activeOnly)

{

return context.OpportunitySet.Where(e => e.StateCode == (int)OpportunityState.Open && e.ModifiedOn.Value >= new DateTime(today.Year, today.Month, today.Day)).OrderBy(e => e.Name).ToList();

}

return context.OpportunitySet.Where(e => e.ModifiedOn.Value >= new DateTime(today.Year, today.Month, today.Day)).OrderBy(e => e.Name).ToList();

}

 

Retrieve CRM Opportunities by Status

public List<Opportunity> RetrieveLastModifiedOpportunitiesByStatus(int status, int recordLastModifiedDays, ref bool isError, ref string errorDetails)

{

System.DateTime today = System.DateTime.Today;

if (recordLastModifiedDays > 0)

{

today = today.AddDays(recordLastModifiedDays * -1);

}

return context.OpportunitySet.Where(e => e.StateCode == status && e.ModifiedOn.Value >= new DateTime(today.Year, today.Month, today.Day)).OrderBy(e => e.Name).ToList();

}

 

public List<Opportunity> RetrieveOpportunitiesByStatus(int status, ref bool isError, ref string errorDetails)

{

return context.OpportunitySet.Where(e => e.StateCode == status).OrderBy(e => e.Name).ToList();

}