Create a LINQ for SQL query

Question

I'm learning Linq and using MVC. I have written a SQL query which I need to convert to a LINQ query.

select TokenID,TokenAsset,packet from TokenTable where id = 6 and packet = ''
and TokenID not in (select TokenID from TokenTable where id=6 and packet <> '')
group by TokenID,TokenAsset,Packet

I kindly ask help to convert the above query to a LINQ query. I know that the SQL query isn't efficient. It would better if you can help me to fix it.

Answer 1

Firstly your SQL query can just be

select distinct TokenID, TokenAsset, packet 
from TokenTable 
where id = 6 and packet = ''

the group by is not that useful since there are no aggregated columns. All selected columns are in the group by clause. Use distinct to achieve the same.

the secondary AND condition for tokenid is also redundant. It is exclusive to the first condition and hence doesn't change the result.

use this LINQ query:

var results = dbcontext.TokenTables
              .Where(t => t.id == 6 && t.Packet == "")
              .Select(t => new { t.TokenId, t.TokenAsset, t.Packet }).Distinct();

project only columns you need for performant calls by avoiding extra data transfer.

Answer 2

Try this one:

var result = Tokens.Where(x=>x.Id==6 && 
                          x.Packet=="" &&
                          !Tokens.Exists(y=>y.TokenID==x.TokenID && 
                                            y.Id==6 && 
                                            y.Packet!="")
                   )
                   .GroupBy(x=>x.ID)
                   .ThenGroupBy(x=>x.TokenAsset)
                   .ThenGroupBy(x=>x.Packet);

Note I suppose that collection Tokens holds all the tokens you have.