在MVC C#中从sql转换为linq

本文关键字：转换 linq sql 中从 MVC | 更新日期: 2023-09-27 18:19:29

我无法从sql转换为linq。我已经附上了我的sql查询和模型。

注意：我们需要使用where条件从国家和目的地开始，并将国家id传递到目的地。

SQL:

select Countries.Name,Destinations.DestinationName,Destinations.DestinationID 
from Destinations
join Countries on Destinations.CountryID = Countries.Id 
where
  Countries.Name like '%Sa%' or Destinations.DestinationName like '%sa%'

代码：

public class Destination
    {
        [Key]
        public virtual Guid Id { get; set; }
        public virtual string DestinationID { get; set; }
        public virtual string DestinationName { get; set; }
        public virtual Guid CountryID { get; set; }
        public virtual string State { get; set; }
        public virtual IEnumerable<HotelDetails> HotelDetails { get; set; }
    }
 public class Country : Entity
    {
       //Comment
        public virtual IEnumerable<Destination> Destinations { get; set; }

    }

 var Context = new HotelDbContext();
            var csdf = (from country in Context.Countries.Where(x =>x.Name.StartsWith(desti))
                        from destina in Context.Destinations.Where(x => x.DestinationName.StartsWith(desti))

                        select new
                        {
                            country.Name,
                            destina.DestinationName,
                            destina.DestinationID
                        }
                          );

在MVC C#中从sql转换为linq

您的sql到linq将如下所示：

var csdf = (from d in Context.Destinations
            join c in Context.Countries on d.CountryId equals c.Id
            where
            c.Name.Contains("Sa") && d.DestinationName.Contains("sa")
            select new {
              c.Name,
              d.DestinationName,
              d.DestinationId
            });

您可以尝试以下操作：

var result = context.Countries
    .Where
    (
        x=>
        x.Name.Contains('Sa') &&
        x.Destinations.Any(d=>d.DestinationName.Contains('sa'))
    )
    .Select
    (
        x=> new Country
        {
            Name = x.Name,
            Destinations = x.Destinations
                .Where(d=>d.DestinationName.Contains('sa'))
                .Select
                (
                    d=>new Destination
                    {
                        Id = d.Id,
                        DestinationID = d.DestinationID ,
                        DestinationName = d.DestinationName ,
                        CountryID = d.CountryID ,
                        State = d.State
                    }
                )
        }
    )
相关文章：