如何将实体框架转换为SQL命令？

Question

我想将此 EF 代码转换为 SQL 命令：

var parents = db.Fuels.Select(A => A.Parent).Distinct();

var data = db.Fuels.Where(B => !parents.Any(A => A == B.ID))

                   .OrderBy(A => A.Type);

我尝试编写SQL命令，但它不起作用：

select *

from Fuel as a

where id not in (select id from Fuel as b

                 where a.ID = b.Parent)

表结构：

ID      INT PK,

Name    NVARCHAR(50) Checked,

Parent  INT FK

表数据

我想要这个输出

Answer 1

这是您正在寻找的查询：;with parents as (select distinct parent as ID from @Fuel)select * from @Fuel where id not in (select Id from @Fuel where Id in(select Id from parents))SQL 小提琴：https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=dc39433e40541b814936f3dfea725c6b