Entity Framework -> How to find the largest table elements

Asked

Viewed 37 times

1

I have the following tables in my base:

Produto
Id | Nome | Preco
1  | fone | 100
2  | copo | 50
3  | pao  | 10

ProdutoStatus

IdProduto | Versao | Preco
1         | 1      | 101
1         | 2      | 110 
1         | 3      | 90
2         | 1      | 55
2         | 2      | 60
3         | 3      | 5

And I need to make a method that searches only the most current versions with their due prices of each product

I am using Entity Framework Core to access this base, already with the mapping of tables implemented.

var data = _context.Produto
           .Include(u => u.ProdutoStatus)
           .Where(x => x.id == x.ProdutoStatus.OrderByDescending(a => a.Versao).FirstOrDefault().IdProduto).ToList()

This code above brings all products and all versions

someone can help me?

EDIT

Look at the entities in these tables

public class Produto{
    
    public int Id {get; set;}
    public string Nome {get; set;}
    public int Preco {get; set;}    
    public virtual ICollection<ProdutoStatus> ProdutoStatus {get; set;}
}

public class ProdutoStatus{
    
    public int IdProduto {get; set;}
    public int Versao {get; set;}
    public int Preco {get; set;}    

    public virtual Produto Produto {get; set;}
}

Important to Know

  • I don’t have access to change the tables
  • I used an example similar to the one I have
sql-server .net entity-framework asp.net-core entity-framework-6

2 answers

0

As I don’t have the classes here can not validate 100%, but by the name of the properties, would be something like this, grouping Productstatus, and sectioning with Max:

var data = _context.Produto
           .GroupBy(g => new 
            {
               g.ProdutoStatus.IdProduto,
               g.ProdutoStatus.Versao,
               g.ProdutoStatus.Preco
            })
           .Select(p => new ProdutoStatus
            { 
                IdProduto = g.Key, 
                Versao = g.Max(x => x.Versao),
                Preco = g.Key.Preco)
            });

  • thanks for the reply, but in this Groupby, I do not have access to the internal properties of the Productostatus Collection, I made an Edit with the definition of Entities.

  • 1

    a doubt, you want only the list of ProductStatus or either Product.ProductStatus where there is only 1 item with the latest version?

  • only Productstatus with the most current version of all Products

  • 1

    var result = _context.Produto&#xA; .Select(p => p.ProdutoStatus.OrderByDescending(o => o.Versao).First())&#xA; .ToList(); That doesn’t solve it then?

  • This LINQ makes a lot of sense, however in the search returns a null line, trying to understand why the foreign key is correct. It makes some correlation between the Products Table and the Productostatus besides the key ?

0


I ended up solving using SQL Brute even, follows Code for those who have the same problem:

public IEnumerable<ProdutoStatus> List()
{
    return _context.ProdutoStatus.FromSqlRaw(@"
        SELECT IDPRODUTO,VERSAO,PRECO 
        FROM ( SELECT IDPRODUTO,VERSAO,PRECO, ROW_NUMBER() OVER(PARTITION BY IDPRODUTO ORDER BY VERSAO DESC) AS rn
            FROM PRODUTOSTATUS) AS T
        WHERE rn = 1
    ").ToList();
}

I hope you help someone

Browser other questions tagged sql-server .net entity-framework asp.net-core entity-framework-6

You are not signed in. Login or sign up in order to post.