C＃EF：加入EF实体类和静态函数 [英] C# EF: joining EF entity class and a static function

问题描述

我尝试加入我的类代表db表和另一个返回一系列日期的类静态函数。我的代码正在工作，但我没有得到期望的价值。

i try to join my class which represent db table and another class static function which return series of date. my code is working but i am not getting desired value.

类 MyData 有一些缺少的日期和我的静态函数返回日期。如果日期静态函数在 MyData 中找不到，那么我想为该行返回 EAmount字段的零值。如果数据在 MyData 中发现，返回静态函数的日期，那么我将返回 EAmount 实际值。

the class MyData has some missing date and my static function return dates. if the date static function return not found in MyData then i want to return zero value for EAmount field for that row. if data found in MyData for the date which return static function then i will return EAmount actual value.

[Table("MyData")]
public partial class MyData
{
    public int ID { get; set; }

    public DateTime? EDate { get; set; }

    public decimal? EAmount { get; set; }

    public DateTime? AddDate { get; set; }

    public DateTime? ModDate { get; set; }
}

我的需求像我离开连接在两个表之间。

my requirement something like i left join between two table.

假设表1具有日期，表2具有日期和金额。加入将基于日期。如果表2中没有匹配的日期表，那么我希望将表1的日期和零显示为值，但如果日期匹配，那么我将从表1获取日期，并从表2中获取金额。这是我简单的要求我正在尝试通过EF加入EF实体表和静态函数。

suppose table 1 has date and table 2 has date and amount. join will be based on date. if there is no matching date in table2 for date present in table 1 then i want to show table 1 date and zero as value but if date match then i will take date from table 1 and amount from table 2. this is my simple requirement which i am trying to do by EF joining a EF entity table and a static function.

所以这里static函数将扮演table1和EF实体类角色为table2 。

so here static function will play the role for table1 and EF entity class play role for table2.

这是我的完整代码。代码执行，但问题是 EAmount值总是为零。

here is my full code. code execute but the problem is EAmount value is getting always zero.

[Table("MyData")]
public partial class MyData
{
    public int ID { get; set; }

    public DateTime? EDate { get; set; }

    public decimal? EAmount { get; set; }

    public DateTime? AddDate { get; set; }

    public DateTime? ModDate { get; set; }
}

public class DateRange
{
    public static List<DateTime> GetDates(DateTime startdate, DateTime enddate)
    {
        List<DateTime> dates = Enumerable.Range(0, (enddate - startdate).Days + 1)
            .Select(day => startdate.AddDays(day)).ToList();

        return dates;
    }
}

public partial class LocalDbContext : DbContext
{
    public LocalDbContext()
        : base("name=LocalTest")
    {
    }

    public virtual DbSet<MyData> MyDatas { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
    }
}

以这种方式调用

calling like this way

    using (var db = new LocalDbContext())
    {

        var result = from p in DateRange.GetDates(new DateTime(2017, 01, 01), new DateTime(2017, 01, 20))
                     join n in db.MyDatas on p equals n.EDate into g
                     from x in g.DefaultIfEmpty()
                     select new
                     {
                         date = p,
                         amount = x == null ? 0 : x.EAmount
                     };

        var xx = result.ToList();
    }

推荐答案

数据不正确您的日期包含时间和由于时间值加入不能正常工作。因此，在实现加入时，您需要排除日期的时间部分，并根据日期实现连接。以下是修改后的查询，以获取正确的数据：

The data is not right because your dates contains time and due to time value join is not working properly. So while implementing join you need to exclude the time part of date and implement the join based on date only. Following is the modified query for you to get proper data:

using (var db = new LocalDbContext())
            {

                var result = from p in DateRange.GetDates(new DateTime(2017, 01, 01), new DateTime(2017, 01, 20))
                             join n in db.MyDatas.Where(x => x.EDate != null) on p.Date equals n.EDate.Value.Date into g
                             from x in g.DefaultIfEmpty()
                             select new
                             {
                                 date = p,
                                 amount = x == null ? 0 : x.EAmount
                             };

                var xx = result.ToList();
            }

这篇关于C＃EF：加入EF实体类和静态函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！