CODEWITHSUNDEEP
c#lambda
parsa
parsa

Reputation: 39

How get max value by lambda query in C#?

I want to get the last number in the code column in C# using the query lambda. Be careful that I want a numerical value, not a list. For example, if the last registered number was 50, I would like this number 50. That is, we can store the query result in a numerical variable so that I can use it elsewhere

var maxcode= dbContext.personel
                      .Select(a => a.Max(w => w.code))
                      .FirstOrDefault();

For example 

 code name     old
 -----------------
 1    Amelia   18
 2    Olivia   27 
 3    Emily    11
 4    Amelia   99

I want to get number 4

If I want to use top(1) to improve the speed?

Upvotes: 1

Views: 5748

Answers (2)

Corey
Corey

Reputation: 16574

While SQL and LINQ share some similarities, they are quite different in how they work. It's best to start with how IEnumerable works with this type of query, then how that translates to IQueryable. In most simple cases the two look exactly the same, by design.

The Select extension for IEnumerable iterates through the sequence and passes each object to the supplied function, collecting the results in a new IEnumerable of the appropriate type. In your code a will be a record rather than a collection.

Essentially Select looks like this under the hood:

public static IEnumerable<TResult> Select<TElement, TResult>(this IEnumerable<TElement> seq, Func<TElement, TResult> selector)
{
    foreach (var item in seq)
        yield return selector(item);
}

In simple words Select is a transformation. It takes objects of one type and passes them through a transform function.

The Max extension - at least the relevant one - processes a sequence of objects, uses the supplied function to pull some value from each object, then returns the largest of those values. It looks a little bit like this pseudo-code:

public static TResult Max<TElement, TResult>(this IEnumerable<TElement> seq, Func<TElement, TResult> valueFunc)
{
    var result = default(TResult);
    foreach (var item in seq)
    {
        var curr = valueFunc(item);
        if (result == default(TResult) || result < curr)
            result = curr;
    }
    return curr;
}

OK that won't compile, but it shows the basic concept.

So if you had an array of Personel objects in memory and wanted to find the largest code then you'd do this:

var maxCode = personel.Max(p => p.code);

The nice thing about LinqToSQL and pretty much all LINQ-like ORMs (Entity Framework, LinqToDB, etc) is that the exact same thing works for IQueryable:

var maxCode = dbContext.personel.Max(p => p.code);

The actual SQL for that will look something like (actual output from LinqToDB code gen):

SELECT 
    Max([t1].[code]) as [c1]
FROM 
    [personel] [t1]

For more interesting queries the syntax differs.

You have two Amelia entries with different ages. Let's say you want to find the age range for each name in your list. This is where grouping comes in.

In LINQ query syntax the query would look something like this:

var nameAges = 
    from p in dbContext.personel
    group p.old by p.name into grp
    select new { name = grp.Key, lowAge = grp.Min(), highAge = grp.Max() };

Grouping is easier in that format. In fluent it looks more like:

var nameAges = dbContext.personel
    .GroupBy(p => p.name, p => p.old)
    .Select(grp => new { name = grp.Key, lowAge = grp.Min(), highAge = grp.Max() };

Or in SQL:

SELECT name, Min(code) AS lowAge, Max(code) AS highAge
FROM personel
GROUP BY name

The moral is, writing LINQ queries is not the same as writing SQL queries... but the concepts are similar. Play around with them, work out how they work. LINQ is a great tool once you understand it.

Upvotes: 3

Dmitry Stepanov
Dmitry Stepanov

Reputation: 2914

This should work: 

var max = dbContext.personel.Max(x => x.code);

Upvotes: 5

Related Questions