LINQ is awesome solution for creating efficient queries with dynamic WHERE conditions

by jasonvonruden 25. January 2011 14:16

I have been using this method for a long time and I just want to share an example of applying dynamic “WHERE” conditions to make more efficient queries.

For example you could create a query using the “LIKE” verb to handle a query with multiple where clauses when you have the potential of having only 1 parameter and you are expecting the other parameter to wildcard.  You will quickly find out that if field that you are wildcarding with just “%” is null you will not match on it when you where expecting a match. This example of applying dynamic “WHERE” conditions totally solves that solution and it is much cleaner code as an added bonus.

Example of just using ‘LIKE’ conditions:

As you can see the query will be less efficient because you are filtering an a column that you may not need to based on the input parameters provided. Must include the function to convert nulls into empty strings, yuck.

SELECT r
FROM XrfReservation r
WHERE ISNULL(r.ReservationProject,'') like :dd and
      ISNULL(r.ReservationName,'') like :di and
ORDER BY r.ReservationProject, r.ReservationName

Example of of applying dynamic “WHERE” conditions:

As I hope that you will see this code looks nicer and is more efficient!

//*******************************************************************
// Get XrfReservations By ReservationProject and ReservationName
//*******************************************************************
public IList<XrfReservation> GetXrfReservationsByProjectName(string _reservationproject, string _reservationname, int _resultpage, int _resultpagesize)
{
    //Paging Logic
    int firstResult = 0;
    if (_resultpage > 1)
    {
        firstResult = (_resultpage - 1) * _resultpagesize;
    }
 
    //Linq - base query
    var xrfReservations = from r in _Session.Query<XrfReservation>()
                          select r;
 
    //Linq: Apply where clauses
    if (!string.IsNullOrEmpty(_reservationproject))
    {
        xrfReservations = xrfReservations.Where(_w => _w.ReservationProject == reservationProject);
    }
 
    if (!string.IsNullOrEmpty(_reservationname))
    {
        xrfReservations = xrfReservations.Where(_w => _w.ReservationName == _reservationname);
    }
 
    //Linq Apply order by.
    var xrfReservationsOrderBy = from o in xrfReservations orderby o.ReservationProject, o.ReservationName select o;
 
    //Linq: Apply paging.
    IList<XrfReservation> xrfReservationsList = xrfReservationsOrderBy.Skip(firstResult).Take(_resultpagesize);
 
    //Execute Query and return list of data
    return xrfReservationsList.ToList();
}

Tags: , ,

c# | LINQ | NHibernate

Display SharePoint List Contents Outside of SharePoint using LINQ to SharePoint

by jasonvonruden 23. July 2010 12:48

My Project was to create a contacts search page for a SharePoint 2007 list library. Out of the box I could not find SharePoint built in support for searching a list library in a user friendly way.  Creating a SharePoint webpart was not an option at this time due to the pain involved in setting up the development environment and deploying the wepart to SharePoint 2007.

To accomplish this task in the required timeframe, I decided to create the contacts search page on an existing ASP.NET Website and use LINQ to SharePoint to communicate with SharePoint 2007 in an authenticated domain environment with anonymous access disabled.  The ASP.NET webpage can then be embedded in SharePoint using the built-in webpage viewer webpart.

Requirements:

  • SharePoint, for this project SharePoint 2007.
  • ASP.NET Website on an internal IIS web server.
  • LINQ to SharePoint which provides a custom query provider for LINQ that allows you to query SharePoint lists using the familiar LINQ syntax. 

Steps:

  1. Download LINQ to SharePoint and Install.
  2. Navigate to LINQ to SharePoint install location. Default location:  “C:\Program Files\BdsSoft LINQ to SharePoint”
  3. Run SPMetal.exe to generate c# LINQ class based on the SharePoint library.
  4. Create SharePoint Data Access Layer Project: Project source:

  5. SharePointDataAccessLayer
  6. Create SharePointDataProvider.cs class.  The trickiest part figuring out how to pass the was Domain Service User Account’s User Name, Password, and Domain to LINQ To SharePoint.  I also found out that the Domain Service User Account must have full permissions to the SharePoint list library for SharePoint’s data services to work for “LINQ To SharePoint” access.

    SharePointDataProvider.cs Source Listing:
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Net;
    using FrameworkCore;
    using BdsSoft.SharePoint.Linq;
    using FrameworkData;
     
    namespace SharePointCore
    {
        public class SharePointDataProvider : IDisposable
        {
            private readonly NetworkCredential _networkCredential;
     
     
            public SharePointDataProvider(NHibernateDataProviderIg _providerm)
            {
                //Get Sharepoint Credentials
                XrfSetting xrfSettingsUserName = _providerm.GetXrfSettingByName("NetworkServiceAccount-UserName");
                XrfSetting xrfSettingPassword = _providerm.GetXrfSettingByName("NetworkServiceAccount-Password");
                XrfSetting xrfSettingDomain = _providerm.GetXrfSettingByName("NetworkServiceAccount-Domain");
                string sharepointUserName = _providerm.EncryptDecryptText(null, xrfSettingsUserName.SettingText);
                string sharepointPassword = _providerm.EncryptDecryptText(null, xrfSettingPassword.SettingText);
                string sharepointDomain = _providerm.EncryptDecryptText(null, xrfSettingDomain.SettingText);
                _networkCredential = new NetworkCredential(sharepointUserName, sharepointPassword, sharepointDomain);    
            }
     
            public void Dispose()
            {
     
            }
     
            //*******************************************************************
            // Get Sharepoint Regulatory Contacts List Items
            //******************************************************************* 
            public IEnumerable<Contacts> GetSharePointRegulatoryContacts(string _name, string _firmcompany)
            {
                //Initialize Vaiables
                IEnumerable<Contacts> contacts = null;
     
                const string SHAREPOINT_SITE_REGULATORY = "http://YourSharePointServer/Sites/SiteName/";
                var sharePointDataContext = new SharePointDataContext(new Uri(SHAREPOINT_SITE_REGULATORY));
                sharePointDataContext.Credentials = _networkCredential;
                var contactsDataContext = sharePointDataContext.GetList<Contacts>();
                contacts = (from c in contactsDataContext select c).AsEnumerable();
     
                //Apply Filters
                const string DEFAULT_WILDCARD_TYPE = "Contains";
                string name = _name == null ? string.Empty : _name.Replace("*", "%").ToLower();
                string firmCompany = _firmcompany == null ? string.Empty : _firmcompany.Replace("*", "%").ToLower();
     
                if (!string.IsNullOrEmpty(_name))
                {
                    string wildcardType = DetermineFilterWildcardType(name, DEFAULT_WILDCARD_TYPE);
                    if (wildcardType == "Equals")
                    {
                        contacts = (from c in contacts where c.Name.ToLower() == name select c);
                    }
                    else if (wildcardType == "StartsWith")
                    {
                        contacts = (from c in contacts where c.Name.ToLower().StartsWith(name) select c);
                    }
                    else if (wildcardType == "EndsWith")
                    {
                        contacts = (from c in contacts where c.Name.ToLower().EndsWith(name) select c);
                    }
                    else if (wildcardType == "Contains")
                    {
                        contacts = (from c in contacts where c.Name.ToLower().Contains(name) select c);
                    }
                }
     
                if (!string.IsNullOrEmpty(_firmcompany))
                {
                    string wildcardType = DetermineFilterWildcardType(firmCompany, DEFAULT_WILDCARD_TYPE);
                    if (wildcardType == "Equals")
                    {
                        contacts = (from c in contacts where c.FirmCompany.ToLower() == firmCompany select c);
                    }
                    else if (wildcardType == "StartsWith")
                    {
                        contacts = (from c in contacts where c.FirmCompany.ToLower().StartsWith(firmCompany) select c);
                    }
                    else if (wildcardType == "EndsWith")
                    {
                        contacts = (from c in contacts where c.FirmCompany.ToLower().EndsWith(firmCompany) select c);
                    }
                    else if (wildcardType == "Contains")
                    {
                        contacts = (from c in contacts where c.FirmCompany.ToLower().Contains(firmCompany) select c);
                    }
                }
     
                return contacts ?? new List<Contacts>();
            }
     
            //*******************************************************************
            // Determine Filter Wildcard Type Routine
            //*******************************************************************       
            private static string DetermineFilterWildcardType(string _inputvalue, string _defaultwildcardtype)
            {
                if (!_inputvalue.Contains("%"))
                {
                    return _defaultwildcardtype;
                }
                if (_inputvalue.StartsWith("%") && _inputvalue.EndsWith("%"))
                {
                    return "Contains";
                }
                if (_inputvalue.EndsWith("%"))
                {
                    return "StartsWith";
                }
                if (_inputvalue.StartsWith("%"))
                {
                    return "EndsWith";
                }
                return _defaultwildcardtype;
            }
     
        }
    }
  7. Create ASP.NET Contacts Search webpage using the SharePointCore Data Access project.

    ContactsSearch.aspx.cs webpage code-behind Source Listing:
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web.UI.WebControls;
    using FrameworkCore;
    using SharePointCore;
     
    namespace BdsgIgWeb.Groups.Bpa
    {
        public partial class ContactsSearch : System.Web.UI.Page
        {
            //*******************************************************************
            // Page Load
            //******************************************************************* 
            protected void Page_Load(object _sender, EventArgs _e)
            {
                if (!Page.IsPostBack)
                {
                    //Get Sharepoint Credentials
                    NHibernateDataProviderIg providerM = new NHibernateDataProviderIg(NHibernateStaticSessionManager.SessionFactoryM.GetCurrentSession());
     
                    //Get Regulatory Contacts List
                    using (var sharePointDataProvider = new SharePointDataProvider(providerM))
                    {
                        const string NAME_EMPTY = null;
                        const string FIRM_COMPANY_EMPTY = null;
     
                        //Get Contacts
                        IEnumerable<Contacts> contacts = sharePointDataProvider.GetSharePointRegulatoryContacts(NAME_EMPTY, FIRM_COMPANY_EMPTY);
     
                        //Build Lookup Dropdown Values.
                        BuildLookupLists(contacts);
                    }
                }
            }
     
            //*****************************************************************************
            // Build Program Lookup Values
            //*****************************************************************************
            private void BuildLookupLists(IEnumerable<Contacts> _contacts)
            {
                //Get Contact Names ComboBox Dropdown Values
                var contactNamesDistinct = (from c in _contacts
                                            orderby c.Name
                                            select new { Name = c.Name == null ? string.Empty : c.Name.Trim() }).Distinct();
                cbContactName.DataSource = contactNamesDistinct;
                cbContactName.DataTextField = "Name";
                cbContactName.DataValueField = "Name";
                cbContactName.DataBind();
                cbContactName.Items.Insert(0, new ListItem("", null));
                cbContactName.Width = 200;
     
                //Get FirmCompany ComboBox Dropdown Values
                var firmCompanyNamesDistinct = (from c in _contacts
                                                orderby c.FirmCompany
                                                select new { FirmCompany = c.FirmCompany == null ? string.Empty : c.FirmCompany.Trim() }).Distinct();
                cbFirmCompanyName.DataSource = firmCompanyNamesDistinct;
                cbFirmCompanyName.DataTextField = "FirmCompany";
                cbFirmCompanyName.DataValueField = "FirmCompany";
                cbFirmCompanyName.DataBind();
                cbFirmCompanyName.Items.Insert(0, new ListItem("", null));
                cbFirmCompanyName.Width = 200;
     
     
            }
     
            //*****************************************************************************
            //Search Regulatory Contacts.
            //*****************************************************************************
            protected void btnSearch_Click(object _sender, EventArgs _e)
            {
                ProcessSearchRequest();
            }
     
            //*****************************************************************************
            // Process Search Request
            //*****************************************************************************
            private void ProcessSearchRequest()
            {
                const int STRING_LENGTH_MAX = 150;
                NHibernateDataProviderIg providerM = new NHibernateDataProviderIg(NHibernateStaticSessionManager.SessionFactoryM.GetCurrentSession());
     
                //Get Regulatory Contacts List
                using (var sharePointDataProvider = new SharePointDataProvider(providerM))
                {
                    //Get Contacts
                    IEnumerable<Contacts> contacts = sharePointDataProvider.GetSharePointRegulatoryContacts(cbContactName.Text, cbFirmCompanyName.Text);
     
                    //Apply Filters
                    var contactsfiltered = from c in contacts
                                           orderby c.Name, c.FirmCompany
                                           select new
                                                      {
                                                          Name = c.Name == null ? string.Empty : c.Name.Trim(),
                                                          ContactDetailsLink = string.Format("http://YourSharePointSite/Sites/SiteName/Lists/Contacts/DispForm.aspx?ID={0}", c.ID),
                                                          Title = c.Title == null ? string.Empty : c.Title.Trim(),
                                                          FirmCompany = c.FirmCompany == null ? string.Empty : c.FirmCompany.Trim(),
                                                          Phone = c.Phone == null ? string.Empty : c.Phone.Trim(),
                                                          Cell = c.Cell == null ? string.Empty : c.Cell.Trim(),
                                                          Email = c.Email == null ? string.Empty : c.Email.Trim(),
                                                          EmailMailTo = c.Email == null ? string.Empty : string.Format("mailto://{0}", c.Email.Trim()),
                                                          Address = c.Address == null ? string.Empty : c.Address.Trim(),
                                                          Notes = c.Notes == null ? string.Empty : bdsgIgFrameworkCoreWebUtils.TruncateAndRemoveRtfFromString(c.Notes, STRING_LENGTH_MAX)
                                                      };
     
                    gvContacts.DataSource = contactsfiltered;
                    gvContacts.DataBind();
                }
            }
        }
    }

Project source: 

 

Tags: , ,

c# | SharePoint | LINQ