将SQL查询转换为Linq-错误

本文关键字：Linq- 错误 转换 SQL 查询 | 更新日期: 2023-09-27 18:20:13

我试图将下面的SQL查询转换为Linq，但遇到了一些错误。即使我使用linqr进行转换，连接也不起作用，在同一工具中，查询也能很好地进行

Sql查询

Select
pj.JobID,
ja.IsAssigned,
pj.JobUniqueID,
u.FirstName,
pj.IsApproved,
pj.IsActive,
pj.IsQuoted,
pj.IsAssigned,
pj.ApprovalDate,
pj.Description,
s.ServiceName,
st.ServiceTypeName
from jobs pj inner join users u on pj.CustomerUserID=u.UserID
inner join services s on pj.ServiceID = s.ServiceID
inner join service_type st on st.ServiceTypeID = pj.ServiceTypeID
left join (select JobID, IsAssigned from job_assigned ) ja on pj.JobID = ja.JobID and ja.IsAssigned =1
left join (select jobid from job_quotes  group by jobid having COUNT(1)<3) j on pj.JobID = j.JobID

Linq : This part i am putting it in the c# code 
from pj in db.Jobs
join u in db.Users on new { CustomerUserID = pj.CustomerUserID } equals new { CustomerUserID = u.UserID }
join ja in (
    (from Job_assigned in db.Job_assigned
    select new {
      Job_assigned.IsAssigned,
      Job_assigned.JobID
    }))
      on new { pj.JobID, IsAssigned = 1 }
  equals new { JobID = Convert.ToInt32(ja.JobID), IsAssigned = (Int64?)Convert.ToInt64(ja.IsAssigned) } into ja_join
from ja in ja_join.DefaultIfEmpty()
join j in (
    (from Job_quotes in db.Job_quotes
    group Job_quotes by new {
      Job_quotes.JobID
    } into g
    where     g.Count() < 3
    select new {
      JobID = (System.Int32?)g.Key.JobID
    })) on new { JobID = pj.JobID } equals new { JobID = Convert.ToInt32(j.JobID) } into j_join
from j in j_join.DefaultIfEmpty()
select new {
  JobID = (System.Int32?)pj.JobID,
  IsAssigned = (System.Boolean?)ja.IsAssigned,
  pj.JobUniqueID,
  u.FirstName,
  pj.IsApproved,
  pj.IsActive,
  pj.IsQuoted,
  Column1 = pj.IsAssigned,
  pj.ApprovalDate,
  pj.Description,
  pj.Services.ServiceName,
  pj.Service_type.ServiceTypeName
}
Error 
The type of one of the expressions in the join clause is incorrect.  Type inference failed in the call to 'GroupJoin'.

我在上面的Linq语句中出现了错误

join ja in (
    (from Job_assigned in db.Job_assigned

将SQL查询转换为Linq-错误

这是因为您的命名类型需要命名参数，例如，这是错误的：

(from Job_assigned in db.Job_assigned
select new {
  Job_assigned.IsAssigned,
  Job_assigned.JobID
}))

这是正确的：

(from Job_assigned in db.Job_assigned
select new {
  JobAssigned = Job_assigned.IsAssigned,
  JobID = Job_assigned.JobID
}))

此外，您对变量的命名也很糟糕，请在其中付出更多的努力，以大大提高可读性。

相关文章：