如何在“按查询分组”中调用没有linq的c#方法

本文关键字：调用 linq 方法 按查询分组 查询 | 更新日期: 2023-09-27 17:54:45

我写了一个工作良好的LINQ查询，该LINQ查询有一个组by语句，使用我的c#方法将DateTime从Miladi转换为hejri。现在我的目的是:在原始sql中编写这个linq查询

这是我的NewsModel类:

public class News
{
    public News()
    {
        Months = new List<string>();
    }        
    public int NewsID { get; set; }
    [Required]        
    public string FaTitle { get; set; }        
    public string FaBody { get; set; }        
    public string EnTitle { get; set; }        
    public string EnBody { get; set; }        
    public bool IsImageActive { get; set; }        
    public string NewsImage { get; set; }        
    public string NewsImage2 { get; set; }        
    public bool IsArchived { get; set; }        
    public bool IsDeleted { get; set; }
    public byte[] RowVersion { get; set; }
    [DataType(DataType.DateTime)]        
    [DisplayFormat(DataFormatString = "{0:yyyy/MM/dd HH:mm:ss}", ApplyFormatInEditMode = true)]
    public DateTime DateUpdate { get; set; }
    [DataType(DataType.DateTime)]        
    [DisplayFormat(DataFormatString = "{0:yyyy/MM/dd HH:mm:ss}", ApplyFormatInEditMode = true)]
    public DateTime DateCreate { get; set; }
    public int CategoryID { get; set; }
    [ForeignKey("CategoryID")]
    public virtual Category Category { get; set; }
    [NotMapped]
    public int Years { get; set; }
    [NotMapped]
    public List<String> Months { get; set; }
}
}

和在我的控制器中:

var Archives = from Records in news
    group Records by new { Years = date.GetYear(Records.DateUpdate, ConvertTime.UTCtoIran) } into GYears
    select new { Years = GYears.Key.Years, Months = (from r in GYears group r by date.GetMonthName(r.DateUpdate) into Gmonths select Gmonths.Key) }.ToExpando();
ViewBag.Archive = Archives;

现在我想用原始sql查询做到这一点，正如你在LINQ查询中看到的，我使用date.GetYear和date.GetMonthName方法是c#方法，但我不能在原始sql查询中使用它们，如"select date.GetYear(foo) ..."

如何在“按查询分组”中调用没有linq的c#方法

根据您的评论，您希望在表记录中获得具有相应月份列表的年份，为此您不需要本地函数:

string query = "select DISTINCT DATEPART(YEAR, dbo.News.DateUpdate) as Year, "
             + "DATEPART(YEAR, dbo.News.DateUpdate) as Month"
             + "from dbo.News";
List<DataHolder> A = db.News.SqlQuery(query).ToList();
var results = (from a in A
                group a by a.Year into grp
                select new
                {
                    Year = grp.Key,
                    Months = grp.Select(x => x.Month).ToList()
                }).ToList();
相关文章：