这种从 SQL 到 LINQ 的转换是否正确

本文关键字：转换 是否 LINQ SQL | 更新日期: 2023-09-27 18:31:18

下面是简单的SQL查询

select RelationshipID from Relationships r
where
r.RequestReceiverID = 3 and
r.RequestSenderID = (select MemberID from Members m
                     where m.Name = 'Cipla')

上面的查询工作正常，给了我预期的结果。

我目前正在尝试学习 LINQ

因此，我尝试将上述SQL查询转换为LINQ

这是我的尝试

var relation = 
    (from relationship in db.RelationShips
     where relationship.RequestReceiverID == LoggedInUserID && 
           relationship.RequestSenderID == 
               (from m in db.Members
                where m.Name == SelectedUserName
                select m.MemberID).FirstOrDefault()
            select relationship).FirstOrDefault();

但是在执行上述查询时，我收到以下错误

Invalid column name 'Member_MemberID'.
Invalid column name 'Member_MemberID1'.

编辑

接下来，我正在尝试更新数据库中的数据

relation.StatusID = (from p in db.StatusMasters
                     where p.Status == Status
                     select p.StatusID).FirstOrDefault();
db.SaveChanges();

根据AgentFire的要求编辑

//Members.cs
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Web;
namespace Munim_File_Sharing.Models
{
    public class Member
    {
        [Key]
        public int MemberID { get; set; }
        public string Name { get; set; }
        public string Password { get; set; }
        public string MobileNo { get; set; }
        public string FileName { get; set; }
        public DateTime MemberSince { get; set; }
        public int MemberTypeID { get; set; }
        public List<Payment> Payments { get; set; }
        public List<Relationship> RelationshipSender { get; set; }
        public List<Relationship> RelationshipReceiver { get; set; }
    }
}

这种从 SQL 到 LINQ 的转换是否正确

获取成员 ID

var memberId = (from m in db.Members
                where m.Name == SelectedUserName
                select m.MemberID).FirstOrDefault();

获取关系

var relation = 
    (from r in db.RelationShips
     where r.RequestReceiverID == LoggedInUserID && 
           r.RequestSenderID == memberId
     select r.RelationshipID).FirstOrDefault();

您的查询，您是否尝试过...

var relationID = 
    (from relationship in db.RelationShips
     where relationship.RequestReceiverID == LoggedInUserID && 
           relationship.RequestSenderID == 
               (from m in db.Members
                where m.Name == SelectedUserName
                select m.MemberID).FirstOrDefault()
            select relationship.RelationshipID).FirstOrDefault();//added RelationshipID to the query

基于问题的编辑...

获取会员编号

var memberId = (from m in db.Members
                where m.Name == SelectedUserName
                select m.MemberID).FirstOrDefault();

获取关系

var relation = 
    (from r in db.RelationShips
     where r.RequestReceiverID == LoggedInUserID && 
           r.RequestSenderID == memberId
     select r).FirstOrDefault();

更新状态..

if (relation != null){
  relation.StatusID = (from p in db.StatusMasters
                     where p.Status == Status
                     select p.StatusID).FirstOrDefault();
db.SaveChanges();
}

再试试这个 有帮助 

var MemberList=from m in db.Members.where(x=>x.m.Name ==    SelectedUserName).select(x=>x.MemberID)).toList();
var relationID = (from relationship in db.RelationShips.where(x=>x.RequestReceiverID == LoggedInUserID && MemberList.contains(x.RequestSenderID))
                   select relationship).FirstOrDefault();

你不能写这样的查询，我认为你需要这样的东西

var relationID = (from relationship in db.RelationShips
where relationship.RequestReceiverID == LoggedInUserID &&
db.Members.FirstOrDefault(x=>x.Name==SelectedUserName).MemberID == relationship.RequestSenderID
select relationship 
).FirstOrDefault().RelationshipID ;
相关文章：