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
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!
// get folder name and file name...var dir = WScript.CreateObject("Scripting.FileSystemObject").GetFolder("C:\\");
// normallyvar 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.jsvar itemNames2 = Enumerable.From(dir.SubFolders).Concat(dir.Files).Select("$.Name").ToArray();
Subquery or Inner query or Nested query is a query in a query. A subquery is usually added in the WHERE Clause of the sql statement. Most of the time, a subquery is used when you know how to search for a value using a SELECT statement, but do not know the exact value in the database.
Subqueries are an alternate way of returning data from multiple tables.
Subqueries can be used with the following sql statements along with the comparision operators like =, <, >, >=, <= etc.
1) Usually, a subquery should return only one record, but sometimes it can also return multiple records when used with operators like IN, NOT IN in the where clause. The query would be like,
SELECT first_name, last_name, subject FROM student_details WHERE games NOT IN ('Cricket', 'Football');
The output would be similar to:
first_name
last_name
subject
-------------
-------------
----------
Shekar
Gowda
Badminton
Priya
Chandra
Chess
2) Lets consider the student_details table which we have used earlier. If you know the name of the students who are studying science subject, you can get their id's by using this query below,
SELECT id, first_name FROM student_details WHERE first_name IN ('Rahul', 'Stephen');
but, if you do not know their names, then to get their id's you need to write the query in this manner,
SELECT id, first_name FROM student_details WHERE first_name IN (SELECT first_name FROM student_details WHERE subject= 'Science');
Output:
id
first_name
--------
-------------
100
Rahul
102
Stephen
In the above sql statement, first the inner query is processed first and then the outer query is processed.
3) Subquery can be used with INSERT statement to add rows of data from one or more tables to another table. Lets try to group all the students who study Maths in a table 'maths_group'.
INSERT INTO maths_group(id, name) SELECT id, first_name || ' ' || last_name FROM student_details WHERE subject= 'Maths'
4) A subquery can be used in the SELECT statement as follows. Lets use the product and order_items table defined in the sql_joins section.
select p.product_name, p.supplier_name, (select order_id from order_items where product_id = 101) as order_id from product p where p.product_id = 101
product_name
supplier_name
order_id
------------------
------------------
----------
Television
Onida
5103
Correlated Subquery
A query is called correlated subquery when both the inner query and the outer query are interdependent. For every row processed by the inner query, the outer query is processed as well. The inner query depends on the outer query before it can be processed.
SELECT p.product_name FROM product p WHERE p.product_id = (SELECT o.product_id FROM order_items o WHERE o.product_id = p.product_id);
NOTE: 1) You can nest as many queries you want but it is recommended not to nest more than 16 subqueries in oracle. 2) If a subquery is not dependent on the outer query it is called a non-correlated subquery.
