Linq to Entities and Last (LastOrDefault)

Linq to Entities and Last (LastOrDefault)

It is my bad luck – every time that I need to show something the team manager, we receive the weirdest exceptions. Today was the LINQ to Entities does not recognize the method ‘XXXXX.LastOrDefault[XXXX]….’ method, and this method cannot be translated into a store expression.

the expression itself was pretty simply – querying by secondary index. When I changed the query to “FirstOrDefault” removed the run time exception, this made me think – FirstOrDefault can be converted to SQL pretty easy – SELECT  TOP 1. How would you translate “LastOrDefault” ?

The concept of first or last implies the usage of some kind of sorting mechanism. When you run regular select there is actually implicit “ORDER BY [PK]” (notice that the order be ASC or DESC, depending of the PK defenition, in general, assuming some kind of sort without explicitly requesting it from the server is probably bad practice) done by the server. this is because of the way the data itself is stored in the sql data table.

So how “Last” should be implemented? Last is actually the first from the end. But how do you define “the end”? lets say you users table which one is the last user? The last registrated user ( order by ID) ? the last registered AND verified user? the last user to log in ? the user with the last lex. name (order by name)?

Conclustion:

If you want to get the last object directly from the sql server (you can use  .ToList().LastOrDefalt() but this will return all the entries and then take the last one – pretty useless with big tables) is to preform OrderByDescending and then take the fist.  

Here is generic function that receives “where” expression and orderBy selector and returns the last entry  matching the criteria.

 protected T GetLastBy(Expression> where, Expression> orderBy)
        {       
            return WorkingSet.OrderByDescending(orderBy).FirstOrDefault(where);
        }

SingleOrDefault() Vs. FirstOrDefault() in LINQ Query

Single() / SingleOrDefault()	First () / FirstOrDefault()
Single() - There is exactly 1 result, an exception is thrown if no result is returned or more than one result.  SingleOrDefault() – Same as Single(), but it can handle the null value.	First() - There is at least one result, an exception is thrown if no result is returned. FirstOrDefault() - Same as First(), but not thrown any exception or return null when there is no result.
Single() asserts that one and only one element exists in the sequence.	First() simply gives you the first one.
When to use Use Single / SingleOrDefault() when you sure there is only one record present in database or you can say if you querying on database with help of primary key of table.	When to use Developer may use First () / FirstOrDefault() anywhere,  when they required single value from collection or database.
Single() or SingleOrDefault() will generate a regular TSQL like "SELECT ...".	The First() or FirstOrDefault() method will generate the TSQL statment like "SELECT TOP 1..."
In the case of Fist / FirstOrDefault, only one row is retrieved from the database so it performs slightly better than single / SingleOrDefault. such a small difference is hardly noticeable but when table contain large number of column and row, at this time performance is noticeable.

SingleOrDefault() Vs. FirstOrDefault() in LINQ Query

linq.js - LINQ for JavaScript

Project Description
linq.js - LINQ for JavaScript

Features

• implement all .NET 4.0 methods and many extra methods (inspiration from Rx, Achiral, Haskell, Ruby, etc...)
• complete lazy evaluation
• full IntelliSense support for VisualStudio
• two versions - linq.js and jquery.linq.js (jQuery plugin)
• support Windows Script Host
• binding for Reactive Extensions for JavaScript(RxJS) and IntelliSense Generator -> see documentation
• NuGet install support(linq.js, linq.js-jQuery, linq.js-Bindings)

90 Methods

Aggregate, All, Alternate, Any, Average, BufferWithCount, CascadeBreadthFirst, CascadeDepthFirst, Catch, Choice, Concat,
Contains, Count, Cycle, DefaultIfEmpty, Distinct, Do, ElementAt, ElementAtOrDefault, Empty, Except, Finally, First, FirstOrDefault, 
Flatten, ForEach, Force, From, Generate, GetEnumerator, GroupBy, GroupJoin, IndexOf, Insert, Intersect, Join, Last, LastIndexOf,
LastOrDefault, Let, Matches, Max, MaxBy, MemoizeAll, Min, MinBy, OfType, OrderBy, OrderByDescending, Pairwise, PartitionBy, 
Range, RangeDown, RangeTo, Repeat, RepeatWithFinalize, Return, Reverse, Scan, Select, SelectMany, SequenceEqual, Share, Shuffle,
Single, SingleOrDefault, Skip, SkipWhile, Sum, Take, TakeExceptLast, TakeFromLast, TakeWhile, ThenBy, ThenByDescending, ToArray,
ToDictionary, ToInfinity,ToJSON, ToLookup, ToNegativeInfinity, ToObject, ToString, Trace, Unfold, Union, Where, Write, WriteLine, Zip

• see details - linq.js Reference & LINQPad

Starting linq.js ver.3.0.0-beta!(2012/07/19)

Get the ver.3.0.4-Beta5 @2013/06/20
or NuGet Install-Package linq.js -Pre, linq.js-jQuery -Pre, linq.js-RxJS -Pre, linq.js-QUnit -Pre
Now TypeScript Generics(0.9) support!

Please try it! and give me a feedback!
http://linqjs.codeplex.com/discussions/376207

Query objects or json

• sample from http://twitter.com/statuses/public_timeline.json

var jsonArray = [
    { "user": { "id": 100, "screen_name": "d_linq" }, "text": "to objects" },
    { "user": { "id": 130, "screen_name": "c_bill" }, "text": "g" },
    { "user": { "id": 155, "screen_name": "b_mskk" }, "text": "kabushiki kaisha" },
    { "user": { "id": 301, "screen_name": "a_xbox" }, "text": "halo reach" }
]
// ["b_mskk:kabushiki kaisha", "c_bill:g", "d_linq:to objects"]
var queryResult = Enumerable.From(jsonArray)
    .Where(function (x) { return x.user.id < 200 })
    .OrderBy(function (x) { return x.user.screen_name })
    .Select(function (x) { return x.user.screen_name + ':' + x.text })
    .ToArray();
// shortcut! string lambda selector
var queryResult2 = Enumerable.From(jsonArray)
    .Where("$.user.id < 200")
    .OrderBy("$.user.screen_name")
    .Select("$.user.screen_name + ':' + $.text")
    .ToArray();

High compatibility with C# Linq

// C# LINQ (delegate)
Enumerable.Range(1, 10)
    .Where(delegate(int i) { return i % 3 == 0; })
    .Select(delegate(int i) { return i * 10; });
// linq.js - anonymous function
Enumerable.Range(1, 10)
    .Where(function(i) { return i % 3 == 0; })
    .Select(function(i) { return i * 10; });
// C# LINQ (lambda)
Enumerable.Range(1, 10).Where(i => i % 3 == 0).Select(i => i * 10);
// linq.js - lambda expression
Enumerable.Range(1, 10).Where("i => i % 3 == 0").Select("i => i * 10");
// $ is default iterator variable like Scala's "_" or Groovy's "it"
Enumerable.Range(1, 10).Where("$ % 3 == 0").Select("$ * 10");
 // "" is shorthand of "x => x" (identity function)
Enumerable.Range(4, 7).Join(Enumerable.Range(8, 5), "", "", "outer,inner=>outer*inner");

// Enumerable.From is wrap from primitive array, string(to charArray), object(to KeyValuePair[]) etc..
var array = [100, 200, 30, 40, 500, 40, 200];
var ex1 = Enumerable.From(array).Distinct().ToArray(); // [100, 200, 30, 40, 500]
var ex2 = Enumerable.From("foobar").ToArray(); // ["f", "o", "o", "b", "a", "r"];
var ex3 = Enumerable.From({foo:10, bar:20}).ToArray(); // [{Key:"foo",Value:10}, {Key:"bar",Value:20}]

// C# - AnonymousType
array.Select((val, i) => new { Value = val, Index = i });
// linq.js - object literal
Enumerable.From(array).Select("val,i=>{Value:val, Index:i}")

jQuery plugin version

// $.Enumerable
$.Enumerable.Range(1, 10).Where("$%2==0").ForEach("alert($)");

// TojQuery - Enumerable to jQuery
$.Enumerable.Range(1, 10)
    .Select(function (i) { return $("").text(i)[0] })
    .TojQuery()
    .appendTo("#select1");

// toEnumerable - jQuery to Enumerable
var sum = $("#select1").children()
    .toEnumerable()
    .Select("parseInt($.text())")
    .Sum(); // 55

IntelliSense vsdoc

video - using with Visual Studio 2010

Launch in another window

video - how to debug linq.js

Launch in another window

with Windows Script Host

// get folder name and file name...

var dir = WScript.CreateObject("Scripting.FileSystemObject").GetFolder("C:\\");
 
// normally
var itemNames = [];
for (var e = new Enumerator(dir.SubFolders); !e.atEnd(); e.moveNext())
{
    itemNames.push(e.item().Name);
}
for (var e = new Enumerator(dir.Files); !e.atEnd(); e.moveNext())
{
    itemNames.push(e.item().Name);
}

// linq.js
var itemNames2 = Enumerable.From(dir.SubFolders).Concat(dir.Files).Select("$.Name").ToArray();