Search This Blog
Showing posts with label LINQ To SharePoint. Show all posts
Showing posts with label LINQ To SharePoint. Show all posts

Thursday, June 28, 2012

Simples queries

EntityList<CustomersItem> Customers = Ctx.GetList<CustomersItem>("Customers");
var CustomerItems = from Customer in Customers                               
                                      select Customer;

foreach (var CustomerItem in CustomerItems)
{
        Console.WriteLine(string.Format("Customer <{0}> aged <{1}> lives in <{2}> - <{3}>",
                       (CustomerItem.Title != null) ? CustomerItem.Title : "",
                       (CustomerItem.Age != null) ? CustomerItem.Age.ToString() : "-",
                       (CustomerItem.City != null && CustomerItem.City.Title != null) ? CustomerItem.City.Title : "",
                       (CustomerItem.City != null && CustomerItem.City.Country != null) ? CustomerItem.City.Country : "")
                       );               
}
------------------------------------------------------------------------------------------------------------------------------------------
EntityList<CustomersItem> Customers = Ctx.GetList<CustomersItem>("Customers");
var CustomerItems = from Customer in Customers     
                                      where Customer.City.Title == "Los Angeles" && Customer.Age > 30                           
                                      select Customer;
 
foreach (var CustomerItem in CustomerItems)
{
        Console.WriteLine(string.Format("Customer <{0}> aged <{1}> lives in <{2}> - <{3}>",
                       (CustomerItem.Title != null) ? CustomerItem.Title : "",
                       (CustomerItem.Age != null) ? CustomerItem.Age.ToString() : "-",
                       (CustomerItem.City != null && CustomerItem.City.Title != null) ? CustomerItem.City.Title : "",
                       (CustomerItem.City != null && CustomerItem.City.Country != null) ? CustomerItem.City.Country : "")
                       );                
}
------------------------------------------------------------------------------------------------------------------------------------------
IEnumerable<IGrouping<CitiesItem, CustomersItem>> CustomersByCity = Ctx.Customers.Where(c => c.Age < 35).GroupBy(c => c.City);
foreach (var CustomerCity in CustomersByCity)
{
        CitiesItem CityGroup = CustomerCity.Key as CitiesItem;
        Console.WriteLine(string.Format("Number of customers aged < 35 living in {0} is {1}",
        CityGroup.Title, CustomerCity.Count()));
}
------------------------------------------------------------------------------------------------------------------------------------------
IEnumerable<IGrouping<CitiesItem, CustomersItem>> CustomersByCity = Ctx.Customers.Where(c => c.Age < 35).ToList().GroupBy(c => c.City);
foreach (var CustomerCity in CustomersByCity)
{
        CitiesItem CityGroup = CustomerCity.Key as CitiesItem;
        Console.WriteLine(string.Format("Number of customers aged < 35 living in {0} is {1}",
        CityGroup.Title, CustomerCity.Count()));
}
-----------------------------------------------------------------------------------------------------------------------------------------
EntityList<CustomersItem> Customers = Ctx.GetList<CustomersItem>("Customers");
EntityList<CitiesItem> Cities = Ctx.GetList<CitiesItem>("Cities");
EntityList<OrdersItem> Orders = Ctx.GetList<OrdersItem>("Orders");
 
var QueryResults = from Customer in Customers
                   join City in Cities on Customer.City.Id equals City.Id
                   join OrderItem in Orders on Customer.Id equals OrderItem.Customer.Id
                   select new { CityName = City.Title, City.Country, Customer.Title, OrderTitle = OrderItem.Title };

------------------------------------------------------------------------------------------------------------------------------------------
List<CustomersItem> Customers = Ctx.GetList<CustomersItem>("Customers").Where(c=>c.City!=null).ToList();
List<CitiesItem> Cities = Ctx.GetList<CitiesItem>("Cities").ToList();
List<OrdersItem> Orders = Ctx.GetList<OrdersItem>("Orders").Where(o => o.Customer != null).ToList();
 
var QueryResults = from Customer in Customers
                   join City in Cities on Customer.City.Id equals City.Id
                   join OrderItem in Orders on Customer.Id equals OrderItem.Customer.Id
                   select new { CityName = City.Title, City.Country, Customer.Title, OrderTitle = OrderItem.Title };
 
-----------------------------------------------------------------------------------------------------------------------------------------
EntityList<CustomersItem> Customers = Ctx.GetList<CustomersItem>("Customers");
EntityList<CitiesItem> Cities = Ctx.GetList<CitiesItem>("Cities");
 
var QueryResults = from Customer in Customers
               join City in Cities on Customer.City.Id equals City.Id
               select new { CityName=City.Title, City.Country, Customer.Title };
 
var Results = QueryResults.ToList();
if (Results.Count > 0)
{
        Results.ForEach(cc => Console.WriteLine("Customer <{0}> lives in <{1}> - <{2}>",
                    (cc.Title != null) ? cc.Title : "-",
                    (cc.CityName != null) ? cc.CityName : "-",
                    (cc.Country != null) ? cc.Country : "-"));
}
else
{
        Console.WriteLine("No data found!");
}
-----------------------------------------------------------------------------------------------------------------------
SPList CustomerList = Web.Lists["Customers"];
SPQuery CustomerCityQuery = new SPQuery();
CustomerCityQuery.Joins =
    "<Join Type='INNER' ListAlias='Cities'>" +
            "<Eq>" +
                "<FieldRef Name='City' RefType='Id' />" +
                "<FieldRef List='Cities' Name='ID' />" +
            "</Eq>" +
    "</Join>";
StringBuilder ProjectedFields = new StringBuilder();
ProjectedFields.Append("<Field Name='CityTitle' Type='Lookup' List='Cities' ShowField='Title' />");
ProjectedFields.Append("<Field Name='CityCountry' Type='Lookup' List='Cities' ShowField='Country' />");
CustomerCityQuery.ProjectedFields = ProjectedFields.ToString();
SPListItemCollection Results = CustomerList.GetItems(CustomerCityQuery);
foreach (SPListItem Result in Results)
{
    SPFieldLookupValue CityTitle = new SPFieldLookupValue(Result["CityTitle"].ToString());
    SPFieldLookupValue CityCountry = new SPFieldLookupValue(Result["CityCountry"].ToString());
 
    Console.WriteLine(string.Format("Customer {0} lives in {1} - {2}",
        Result.Title,
        CityTitle.LookupValue,
        CityCountry.LookupValue));
} 
---------------------------------------------------------------------------------------------------------------------------------------
SPList CustomerList = Web.Lists["Orders"];
SPQuery CustomerCityQuery = new SPQuery();
CustomerCityQuery.Joins =
    "<Join Type='INNER' ListAlias='Customers'>" +
            "<Eq>" +
                "<FieldRef Name='Customer' RefType='Id' />" +
                "<FieldRef List='Customers' Name='ID' />" +
            "</Eq>" +
    "</Join>" +
    "<Join Type='INNER' ListAlias='Cities'>" +
            "<Eq>" +
                "<FieldRef List='Customers' Name='City' RefType='Id' />" +
                "<FieldRef List='Cities' Name='ID' /> " +
            "</Eq>" +
    "</Join>";
 
StringBuilder ProjectedFields = new StringBuilder();
ProjectedFields.Append("<Field Name='CityTitle' Type='Lookup' List='Cities' ShowField='Title' />");
ProjectedFields.Append("<Field Name='CityCountry' Type='Lookup' List='Cities' ShowField='Country' />");
ProjectedFields.Append("<Field Name='CustomerTitle' Type='Lookup' List='Customers' ShowField='Title' />");
ProjectedFields.Append("<Field Name='CustomerAge' Type='Lookup' List='Customers' ShowField='Age' />");
CustomerCityQuery.ProjectedFields = ProjectedFields.ToString();
 
SPListItemCollection Results = CustomerList.GetItems(CustomerCityQuery);
foreach (SPListItem Result in Results)
{
    SPFieldLookupValue CityTitle =
        new SPFieldLookupValue((Result["CityTitle"] != null) ? Result["CityTitle"].ToString() : "");
    SPFieldLookupValue CityCountry =
        new SPFieldLookupValue((Result["CityCountry"] != null) ? Result["CityCountry"].ToString() : "");
    SPFieldLookupValue CustomerTitle =
        new SPFieldLookupValue((Result["CustomerTitle"] != null) ? Result["CustomerTitle"].ToString() : "");
    SPFieldLookupValue CustomerAge =
        new SPFieldLookupValue((Result["CustomerAge"] != null) ? Result["CustomerAge"].ToString() : "");
 
    Console.WriteLine(string.Format("Customer {0} living in {1} - {2} has ordered #{3}",
       CustomerTitle.LookupValue,
       CityTitle.LookupValue,
       CityCountry.LookupValue,
       Result.Title));
}    
----------------------------------------------------------------------------------------------------------------------------------------

 
static void AddCustomer(string CustomerName)
{
        try
        {
            EntityList<CustomersItem> Customers = Ctx.GetList<CustomersItem>("Customers");
            CustomersItem NewCustomer = new CustomersItem();
            NewCustomer.Title = CustomerName;
            Customers.InsertOnSubmit(NewCustomer);
            Ctx.SubmitChanges();
            Console.WriteLine("Customer added");
        }
        catch (SPDuplicateValuesFoundException)
        {
            Console.WriteLine("The customer was not added because it would have created a duplicate entry");
        }
        catch (ChangeConflictException ConflictException)
        {
            Console.WriteLine("The customer was not added because a conflict occured:" + ConflictException.Message);
        }
        catch (Exception Ex)
        {
            Console.WriteLine("The customer was not added because the following error occured:" + Ex.Message);
        }  
}
-----------------------------------------------------------------------------------------------------------------------------------------
try
{
    EntityList<CustomersItem> Customers = Ctx.GetList<CustomersItem>("Customers");
    for(int i=0;i<10;i++)
    {
        CustomersItem NewCustomer = new CustomersItem();    
        NewCustomer.Title = String.Format("Customer {0}",i.ToString());
        Customers.InsertOnSubmit(NewCustomer);
    }
    Ctx.SubmitChanges();
    Console.WriteLine("Customer added");
}
catch (SPDuplicateValuesFoundException)
{
    Console.WriteLine("The customer was not added because it would have created a duplicate entry");
}
catch (ChangeConflictException ConflictException)
{
    Console.WriteLine("The customer was not added because a conflict occured:" + ConflictException.Message);
}
catch (Exception Ex)
{
    Console.WriteLine("The customer was not added because the following error occured:" + Ex.Message);
} 
------------------------------------------------------------------------------------------------------------------------------------------
 
static void UpdateCustomer(int CustomerId, string CityName)
{ 
        EntityList<CitiesItem> Cities = Ctx.GetList<CitiesItem>("Cities");
        var CitiesItm = from City in Cities
                        where City.Title == CityName
                        select City;
        
        CitiesItem CityItem = null;
        foreach (var Cit in CitiesItm)
            CityItem = Cit;
        
        if (CityItem == null)
        {
            Console.WriteLine("City not found");
            Environment.Exit(0);
        }
        
        
        EntityList<CustomersItem> Customers = Ctx.GetList<CustomersItem>("Customers");
        var CustomerItems = from Customer in Customers
                            where Customer.Id == CustomerId
                            select Customer;
        
        List<CustomersItem> Results = CustomerItems.ToList();
        if (Results.Count > 0)
        {
        
            try
            {
                Results.ForEach(CustomerItem => CustomerItem.City = CityItem);
                Ctx.SubmitChanges();
                Console.WriteLine("Customer {0} updated with City {1}!",
                    CustomerId.ToString(), CityName);
            }
            catch (ChangeConflictException ConflictException)
            {
                Console.WriteLine("The customer was not updated because a conflict occured:" + ConflictException.Message);
            }
            catch (Exception Ex)
            {
                Console.WriteLine("The customer was not updated because the following error occured:" + Ex.Message);
            }
        }
        else
        {
            Console.WriteLine("Customer {0} not found!", CustomerId.ToString());
        }    
}
------------------------------------------------------------------------------------------------------------------------------------------
 
static void DeleteCustomer(int CustomerId)
{
        EntityList<CustomersItem> Customers = Ctx.GetList<CustomersItem>("Customers");
        var QueryResults = from Customer in Customers
                           where Customer.Id == CustomerId
                           select Customer;
        List<CustomersItem> ReturnedCustomers = QueryResults.ToList();
        if (ReturnedCustomers.Count > 0)
        {
            try
            {
                Customers.DeleteOnSubmit(ReturnedCustomers[0]);
                Ctx.SubmitChanges();
            }
            catch (ChangeConflictException ConflictException)
            {
                Console.WriteLine("The customers were not updated because a conflict occured:" + ConflictException.Message);
            }
            catch (Exception Ex)
            {
                Console.WriteLine("The customers were not updated because the following error occured:" + Ex.Message);
            }
        }
        else
        {
            Console.WriteLine("Customer {0} not found", CustomerId.ToString());
        }
}
------------------------------------------------------------------------------------------------------------------------------------------
 
static void UpdateAllCustomers(string CityName)
{
        EntityList<CitiesItem> Cities = Ctx.GetList<CitiesItem>("Cities");
        var CitiesItm = from City in Cities
                        where City.Title == CityName
                        select City;
        
        CitiesItem CityItem = null;
        foreach (var Cit in CitiesItm)
            CityItem = Cit;
        
        if (CityItem == null)
        {
            Console.WriteLine("City not found");
            Environment.Exit(0);
        }
        
        
        EntityList<CustomersItem> Customers = Ctx.GetList<CustomersItem>("Customers");
        var CustomerItems = from Customer in Customers
                            select Customer;
        
        List<CustomersItem> Results = CustomerItems.ToList();
        if (Results.Count > 0)
        {
        
            try
            {
                Results.ForEach(CustomerItem => CustomerItem.City = CityItem);
                Ctx.SubmitChanges();
                Console.WriteLine("All the customer were updated with City {0}!",
                    CityName);
            }
            catch (ChangeConflictException ConflictException)
            {
                Console.WriteLine("The customers were not updated because a conflict occured:" + ConflictException.Message);
            }
            catch (Exception Ex)
            {
                Console.WriteLine("The customers were not updated because the following error occured:" + Ex.Message);
            }
        }
        else
        {
            Console.WriteLine("No customer currently exist");
        }
}
------------------------------------------------------------------------------------------------------------------------------------------
Posted by at No comments:
Labels: ,

Tuesday, June 26, 2012

LINQ To SharePoint: Working with Created, CreatedBy, Modified and ModifiedBy

LINQ To SharePoint: Working with Created, CreatedBy, Modified and ModifiedBy
LINQ to SharePoint is a great tool to perform queries against a SharePoint server since the 2010 version. Unlike the classical CAML queries, it allows to use a strongly-typed entity model and LINQ query syntax to query list data.

The SPMetal command
The first step to use LINQ to SharePoint is to run the SPMetal tool in order to create the entity model from an existent SharePoint site. This tool is located at 14\bin. Here’s a sample on how to use it:
SPMetal /web:http://mysharepointsite:9999 /code:Model.cs
This command will create a C# code file containing the entity model, in 14\bin\Model.cs. After adding this file to our project, we can perform queries using LINQ to SharePoint. For example, this server-side code, outputs the titles for all the items in “MyList” where the title length is at least 10 characters long:
StringBuilder output = new StringBuilder();
using (ModelDataContext model = new ModelDataContext(SPContext.Current.Site.Url))
{
    foreach (MyListItem itemWithoutTitle in model.MyList.Where(x => x.Title.Length >= 10))
    {
        output.AppendLine(itemWithoutTitle.Title);
    }
}
The missing fields
By default, the Created, CreatedBy, Modified and ModifiedBy fields are not created by SPMetal. However, the framework offers a way of extending the object-relational mapping system of the LINQ to SharePoint provider. In other words, we can easily use those fields after telling LINQ to SharePoint how to retrieve and update them from the content database.
We will extend the base entity class of our model (“Item” class) in a new code file (we can call it “ModelExtensions.cs” for example):
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint.Linq;
using Microsoft.SharePoint;
public partial class Item : ICustomMapping
{
    [CustomMapping(Columns = new String[] { "Modified", "Created", "Editor", "Author" })]
   public void MapFrom(object listItem)
    {
        SPListItem item = (SPListItem)listItem;
        this.Modified = (DateTime)item["Modified"];
        this.Created = (DateTime)item["Created"];
        this.CreatedBy = (string)item["Author"];
        this.ModifiedBy = (string)item["Editor"];
    }
    public void MapTo(object listItem)
    {
        SPListItem item = (SPListItem)listItem;
        item["Modified"] = this.Modified;
        item["Created"] = this.Created;
        item["Author"] = this.CreatedBy;
        item["Editor"] = this.ModifiedBy;
    }
    public void Resolve(RefreshMode mode, object originalListItem, object databaseObject)
    {
        SPListItem originalItem = (SPListItem)originalListItem;
        SPListItem databaseItem = (SPListItem)databaseObject;
        DateTime originalModifiedValue = (DateTime)originalItem["Modified"];
        DateTime dbModifiedValue = (DateTime)databaseItem["Modified"];
        DateTime originalCreatedValue = (DateTime)originalItem["Created"];
        DateTime dbCreatedValue = (DateTime)databaseItem["Created"];
        string originalCreatedByValue = (string)originalItem["Author"];
        string dbCreatedByValue = (string)databaseItem["Author"];
        string originalModifiedByValue = (string)originalItem["Editor"];
        string dbModifiedByValue = (string)databaseItem["Editor"];
        if (mode == RefreshMode.OverwriteCurrentValues)
        {
            this.Modified = dbModifiedValue;
            this.Created = dbCreatedValue;
            this.CreatedBy = dbCreatedByValue;
            this.ModifiedBy = dbModifiedByValue;
        }
        else if (mode == RefreshMode.KeepCurrentValues)
        {
            databaseItem["Modified"] = this.Modified;
            databaseItem["Created"] = this.Created;
            databaseItem["Author"] = this.CreatedBy;
            databaseItem["Editor"] = this.ModifiedBy;
        }
        else if (mode == RefreshMode.KeepChanges)
        {
            if (this.Modified != originalModifiedValue)
            {
                databaseItem["Modified"] = this.Modified;
            }
            else if (this.Modified == originalModifiedValue && this.Modified != dbModifiedValue)
            {
                this.Modified = dbModifiedValue;
            }
            if (this.Created != originalCreatedValue)
            {
                databaseItem["Created"] = this.Created;
            }
            else if (this.Created == originalCreatedValue && this.Created != dbCreatedValue)
            {
                this.Created = dbCreatedValue;
            }
            if (this.CreatedBy != originalCreatedByValue)
            {
                databaseItem["Author"] = this.CreatedBy;
            }
            else if (this.CreatedBy == originalCreatedByValue && this.CreatedBy != dbCreatedByValue)
            {
                this.CreatedBy = dbCreatedByValue;
            }
            if (this.ModifiedBy != originalModifiedByValue)
            {
                databaseItem["Editor"] = this.ModifiedBy;
            }
            else if (this.ModifiedBy == originalModifiedByValue && this.ModifiedBy != dbModifiedByValue)
            {
                this.ModifiedBy = dbModifiedByValue;
            }
        }
    }
    public DateTime Modified { get; set; }
    public DateTime Created { get; set; }
    public string CreatedBy { get; set; }
    public string ModifiedBy { get; set; }
}
For extended information of how the ICustomMapping interface works, you can check these MSDN articles: ICustomMapping Members and RefreshMode Enumeration.
After adding this file to our project, we can use Modified, Created, CreatedBy and ModifiedBy in our queries:
StringBuilder output = new StringBuilder();
using (ModelDataContext model = new ModelDataContext(SPContext.Current.Site.Url))
{
    DateTime date = DateTime.Parse(“Thu, 05 May 2011 12:46:00 GMT”);
    foreach (MyListItem itemCreatedAfterDate in model.MyList.Where(x => x.Created > date))
    {
        output.AppendLine(itemCreatedAfterDate.Title);
    }
}
Keep in mind that the Author and Editor fields identify users. These strings may have more information than what you need. An easy way of parsing this string to extract the information you need is to create a new SPFieldUserValue with the current SPWeb and the string. Then you can extract the actual SPUser from SPFieldUserValue.User.
I hope you find this code useful as I do. Enjoy!

Posted by at No comments:
Labels:
Subscribe to: Posts (Atom)