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.
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.
not in part?
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.
distinct instead of group by is good. However, suggesting that the second condition is redundant without knowing the data structure can be incorrect. You don't know what values (or NULLs) the TokenID field has. Notice that the "id" and "TokenID" seem to be two different fields.
packet <> ''. However, the second condition may eliminate even more. For instance, you are assuming that "TokenID" is the key for the "TokenTable", but what if it is not? and what is "id"? If duplicate values for "TokenID" exist for some "id"s: let's say (id=6,ToketID=2,Packet=''),(id=6,TokenID=2,Packet='p'), the first condition will eliminate the second row, but then the second condition will eliminate the first row as well.