Autocomplete - MVC3, RAZOR, LINQ, JQuery

Hi!
This infernal autocomplete took me two days, but I finally made it. Territories is an entity from Northwind schema.

DAO


        public IQueryable<string> getTerritoryDescription(string searchstring)
        {

            NorthwindDataContext lObjND = new NorthwindDataContext();
            var suggestions = from p in lObjND.Territories select p.TerritoryDescription;
            var namelist = suggestions.Where(n => n.ToLower().StartsWith(searchstring.ToLower()));
            return namelist;
        }

CONTROLLER


        public JsonResult AutoCompleteSuggestions(string term)
        {
            var suggestions = (new TerritoriesDAO()).getTerritoryDescription(term);
            var namelist = suggestions.Where(n => n.ToLower().StartsWith(term.ToLower()));
            return Json(namelist, JsonRequestBehavior.AllowGet);
        }

Attention: if you try to use another parameter name instead of term, it will not work. Something like (string searchstring) doesn't work.

VIEW

<script src="../../Scripts/jquery-ui-1.8.11.js" type="text/javascript"></script>
<script src="../../Scripts/jquery-ui-1.8.11.min.js" type="text/javascript"></script>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.1/jquery-ui.min.js"></script>

<script type="text/javascript">
    $(function () {
        $("#SearchString").autocomplete({
            source: "/Territories/AutocompleteSuggestions",
            minLength: 1  });
    });
</script>

.
.
@using (Html.BeginForm())
{
    <p>
        Find by name: @Html.TextBox("SearchString") 
        <input type="submit" value="Search" /></p>
}


And that's it!

LINQ - Can't use predicate when joining two or more tables

Hi!

Scenario
Imagine that you're trying (like me) to write a "dynamic" where to your select


            var myTerritories = (from te in lObjDC.Territories
                                 join re in lObjDC.Regions
                                     on te.RegionID equals re.RegionID
                                 select new
                                 {
                                     TerritoryID = te.TerritoryID,
                                     TerritoryDescription = te.TerritoryDescription,
                                     RegionDescription = re.RegionDescription
                                 }).Where(w => w.TerritoryDescription.Contains(pStrSearchString));

To accomplish such thing you'll have to use predicate concept.

var predicate = PredicateBuilder.True<Territory>();

The problem is:
When you execute join using link, it's gonna result in a anonymous type of object. But to build a predicate you need to reference an object. So, if I try something like that:

var predicate = PredicateBuilder.True<Territory>();
predicate = predicate.And(e => e.TerritoryDescription.ToLower().Contains(pStrSearchString.ToLower().Trim()));

            var myTerritories = (from te in lObjDC.Territories
                                 join re in lObjDC.Regions
                                     on te.RegionID equals re.RegionID
                                 select new
                                 {
                                     TerritoryID = te.TerritoryID,
                                     TerritoryDescription = te.TerritoryDescription,
                                     RegionDescription = re.RegionDescription
                                 }).Where(predicate );

pStrSearchString - it's a parameter variable from my code which I borrow and wrote here.

I'm gonna receive  a design mode error. Why? Because the predicate is using Territory as reference. But the result of the select is anonymous.

Solution
So if I can't use predicate when it comes to join, what can I do? Well, why don't you create a view v_Territory . The view will resolve the problem. With that in mind, the select will result in something like taht:

var predicate = PredicateBuilder.True<Territory>();
predicate = predicate.And(e => e.TerritoryDescription.ToLower().Contains(pStrSearchString.ToLower().Trim()));

            var myTerritories = lObjDC.Territories.Where(predicate);

Remember that your view will be something like that

select a.TerritoryID, a.TerritoryDescription, b.RegionDescription
from Territories a
join Region b
on a.RegionID = b.RegionID

Why all of this explanation about predicate? If you are a more or less experienced programmer, you already know that dynamic where will be eventually necessary.

If you don't have the class PredicateBuilder built-in. Here is the code:

using System;
using System.Linq;
using System.Linq.Expressions;
using System.Collections.Generic;

public static class PredicateBuilder
{
    public static Expression<Func<T, bool>> True<T>() { return f => true; }
    public static Expression<Func<T, bool>> False<T>() { return f => false; }

    public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> expr1,
                                                        Expression<Func<T, bool>> expr2)
    {
        var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
        return Expression.Lambda<Func<T, bool>>
              (Expression.OrElse(expr1.Body, invokedExpr), expr1.Parameters);
    }

    public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> expr1,
                                                         Expression<Func<T, bool>> expr2)
    {
        var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
        return Expression.Lambda<Func<T, bool>>
              (Expression.AndAlso(expr1.Body, invokedExpr), expr1.Parameters);
    }
}

MVC3-RAZOR-LINQ :: searchstring appears null - ERROR

There are many examples of AutoComplete funcionality on the internet like this one bellow

This is a typical method of a controler

        public JsonResult AutoCompleteSuggestions(string searchstring)
        {
            var suggestions = (new TerritoriesDAO()).getTerritoryDescription(term);
            var namelist = suggestions.Where(n => n.ToLower().StartsWith(term.ToLower()));
            return Json(namelist, JsonRequestBehavior.AllowGet);
        }

Problem is: when the method is called by the view, search string always comes null.

Debugging on firefox you're gona find out that the parameter name is term. So, if you name it as searchstring, it will always be null. Why this happens? I have no idea.

--
[16:21:27.306] GET http://localhost:3289/Territories/AutocompleteSuggestions?term=w [HTTP/1.1 200 OK 0 ms]

--

Change the parameter name to term and magically the value will appear!

        public JsonResult AutoCompleteSuggestions(string term)
        {
            var suggestions = (new TerritoriesDAO()).getTerritoryDescription(term);
            var namelist = suggestions.Where(n => n.ToLower().StartsWith(term.ToLower()));
            return Json(namelist, JsonRequestBehavior.AllowGet);
        }

Dynamic OrderBy using LINQ

It took me hours to figure out how to make dynamic orderby, using LINQ. It's not easy at all.
There are a lot of abstraction which almost fried my brain.

So let's do it step by step.

As schema of database I'm using northwind.
The table I'm using is Territory.
But like me, you probably came to the conclusion that using the model created by LINQ through database bind is not a good idea. So, I created a class called TerritoryModel. This class is in the folder RAZOR. That's why you're seeing  RAZOR.Models.TerritoryModel as the type of the IOrderedEnumerable result.

Notice that the result of the method getAll is a IOrderedEnumerable and not a IQueryable, which would be expected.

1 -  Variables to define.

            //The LINQ context
            NorthwindDataContext lObjDC = new NorthwindDataContext();


           
            //The function you'll need to dynamically change the field by which you're going to order your result.
            Func<TerritoryModel, object> selector = null;



           
            //The variable which will receive the result of the ordered result.
            IOrderedEnumerable<TerritoryModel> lMT = null;

2 - Swtich

            //I wrote a very simple switch. This is the part of the program that does the trick of dynamically change the column which will order the table Territory.

            #region Selector
            switch (pIntSortBy)
            {
                case 1:
                        selector = a => a.TerritoryID;
                    break;
                case 2:
                        selector = a => a.TerritoryDescription;
                    break;
                case 3:
                        selector = a => a.RegionDescription;
                    break;
            }


3 - The IQueryable select

            var myTerritories = (from te in lObjDC.Territories
                                 join re in lObjDC.Regions
                                     on te.RegionID equals re.RegionID
                                 select new
                                 {
                                     TerritoryID = te.TerritoryID,
                                     TerritoryDescription = te.TerritoryDescription,
                                     RegionDescription = re.RegionDescription
                                 });

4 -The orderBy. An IOrderedEnumerable result.

            if (pBlnIsAsc)
            {
                lMT = myTerritories.Select(p => new TerritoryModel(p.TerritoryID, p.TerritoryDescription, p.RegionDescription)).OrderBy(selector);
            }
            else
            {
                lMT = myTerritories.Select(p => new TerritoryModel(p.TerritoryID, p.TerritoryDescription, p.RegionDescription)).OrderByDescending(selector);
            }

 Why am'I working with IOrderedEnumerable? Well, in the moment you type OrderBy or OrderByDescending, it stops to be IQueryable and starts to be IOrderedEnumerable.

In the end, we'll have a function like that:

 
        public IOrderedEnumerable<RAZOR.Models.TerritoryModel> getAll(int pIntSortBy, bool pBlnIsAsc)
        {
            NorthwindDataContext lObjDC = new NorthwindDataContext();

            Func<TerritoryModel, object> selector = null;
            IOrderedEnumerable<TerritoryModel> lMT = null;
           
            #region Selector
            switch (pIntSortBy)
            {
                case 1:
                        selector = a => a.TerritoryID;
                    break;
                case 2:
                        selector = a => a.TerritoryDescription;
                    break;
                case 3:
                        selector = a => a.RegionDescription;
                    break;
            }
            #endregion          

            var myTerritories = (from te in lObjDC.Territories
                                 join re in lObjDC.Regions
                                     on te.RegionID equals re.RegionID
                                 select new
                                 {
                                     TerritoryID = te.TerritoryID,
                                     TerritoryDescription = te.TerritoryDescription,
                                     RegionDescription = re.RegionDescription
                                 });

            if (pBlnIsAsc)
            {
                lMT = myTerritories.Select(p => new TerritoryModel(p.TerritoryID, p.TerritoryDescription, p.RegionDescription)).OrderBy(selector);
            }
            else
            {
                lMT = myTerritories.Select(p => new TerritoryModel(p.TerritoryID, p.TerritoryDescription, p.RegionDescription)).OrderByDescending(selector);
            }
           
            return lMT;
        }

Et voilà!

LINQ - Join tables and populate model class

Hi!

I was wandering how to join one or more tables and populate my enumerable result with my class model.

My Class Model - TerrytoryModel
Table - Territory, Region

1 - The result of the join is a anonymous enumerable list.
2 - Populate a class with the result.



            var myTerritories = (from te in lObjDC.Territories
                                 join re in lObjDC.Regions
                                     on te.RegionID equals re.RegionID
                                 select new
                                 {
                                     TerritoryID = te.TerritoryID,
                                     TerritoryDescription = te.TerritoryDescription,
                                     RegionDescription = re.RegionDescription
                                 }
                                 ).Select(p => new TerritoryModel(p.TerritoryID, p.TerritoryDescription, p.RegionDescription));


3 - The result of myTerritories is a Enumerable list of TerritoryModel.



PS:
Territory and Region are tables from the Northwind database.

Retrieving data using LINQ, creating and populating object at the same time

 Hi!

I found this way of create and populate object and, at the same time, retrieve data using LINQ.


        public CustomerModel find(string id)
        {
            DataClasses1DataContext lObjDC = new DataClasses1DataContext();

            var varCustomerModel = lObjDC.Customers.Where(x => x.CustomerID == id).Select(p => new CustomerModel(p.CustomerID, p.CompanyName)).Single();

            return varCustomerModel;
        }

Insert using LINQ

 Hi!

This is a method from a project I'm currently working at.

        public bool insert(string id, string companyName)
        {
            DataClasses1DataContext lObjDC = new DataClasses1DataContext();

            int lCount = lObjDC.Customers.Where(x => x.CustomerID == id).Count();




            //If customer doesn't exist.
            if (lCount == 0)
            {
                Customer lObjCTM = new Customer();
                lObjCTM.CustomerID = id;
                lObjCTM.CompanyName = companyName;
                lObjDC.Customers.InsertOnSubmit(lObjCTM);
                lObjDC.SubmitChanges();
                return true;
            }
            return false;
        }


PS: I should have written an exist method instead of using Count() inside the insert method.

Update using LINQ

 Hi!

This is a method from a project I'm currently working at.

        public void update(string id, string companyName)
        {
            DataClasses1DataContext lObjDC = new DataClasses1DataContext();
            var lObjCustomer = lObjDC.Customers.Where(x => x.CustomerID == id).Single();
            lObjCustomer.CompanyName = companyName;
            lObjCustomer.CustomerID = id;
            lObjDC.SubmitChanges();         
        }