Get response with null fields

[ingezone]

Your code works perfect!!! Thank you very much...

Using your code as a guide, I did the following but I get only null values, I can't find the error.

TABLE 1
> ------------+--------------+--------------+-----------+
> -  Device - |--  CoordX  --|--  CoordY  --| - State - |
> ------------+--------------+--------------+-----------+
>    ZZ100    |   11111111   |    1110000   |     1     |  
> ------------+--------------+--------------+-----------+
>    ZZ200    |   55555555   |    5550000   |     1     |     
> ------------+--------------+--------------+-----------+
>    ZZ300    |   99999999   |    9990000   |     1     |

TABLE 2
> ---------+-----------------------+----------------+----------------+----------------+----------------+
> - value -| ----- datetime ------ | --   year   -- | --  CoordX  -- | --  CoordY  -- |
> ---------+-----------------------+----------------+----------------+----------------+----------------+
>    777   |  2020-12-16 02:00:00  |      2020      |    99999999    |     9990000    |
> ---------+-----------------------+----------------+----------------+----------------+----------------+
>    90    |  2021-01-01 hh:mm:ss  |      2021      |    99999999    |     9990000    |
> ---------+-----------------------+----------------+----------------+----------------+----------------+
>    55    |  2021-02-15 hh:mm:ss  |      2021      |    99999999    |     9990000    |
> ---------+-----------------------+----------------+----------------+----------------+----------------+
>    500   |  2021-11-29 hh:mm:ss  |      2021      |    99999999    |     9990000    |
> ---------+-----------------------+----------------+----------------+----------------+----------------+
>    115   |  2021-12-31 hh:mm:ss  |      2021      |    99999999    |     9990000    |
> ---------+-----------------------+----------------+----------------+----------------+----------------+

The result of my query should be:

> -------------+-------------+
> -   month  - | -- value -- |
> -------------+-------------+
>      01      |      90     |  
> -------------+-------------+
>      02      |      55     |  
> -------------+-------------+
    :         :
     months        nulls
    :             :
> -------------+-------------+
>      11      |     500     |  
> -------------+-------------+
>      12      |     115     |  
> -------------+-------------+

My code snippet:

public static IEnumerable<DateTime> GetDates(int year)
{
    return Enumerable.Range(1, 12)
             .Select(month => new DateTime(year, month, 01));
}

var dateFilter = DateTime.Parse("2021-01-01 00:00:00");
    var query =
            from t2 in table2
            where t2.year == dateFilter.Year
            join t1 in table1 on new { t2.CoordX, t2.CoordY } equals new { t1.CoordX, t1.CoordY }
    select new
    {
        Day = t2.datetime.Date, 
        Value = t2.value,
    };
    var rawData = query.ToLinqToDB();

    var enrichedData =
        from d in GetDates(dateFilter.Year)
        join r in rawData.AsEnumerable() on d equals r.Day into gj
        from r in gj.DefaultIfEmpty()
        select new
        {
            month = d.Month,
            value = r?.Value,
        };

    var result = enrichedData.ToList(); 

[sdanyliv]

Probably join with Table1 is not needed. I have left that because it was your original query.

[ingezone]

join t1 in table1.Where(x => x.device == "ZZ300") on new { t2.CoordX, t2.CoordY } equals new { t1.CoordX, t1.CoordY }

The union is necessary because I perform a filter for each device that is in table 1.

[ingezone]

I forgot to comment what I am trying to do now, with this last code I need to implement a query that returns me a range of months from 01 to 12, if any month does not exist in table 2, the value field of the missing month must be filled with a null value.

[ingezone]

`var rawData = query.ToLinqToDB();

The 'rawData' variable is correctly delivering the data, but when passing through the 'enrichedData' variable it only returns null values. `

[sdanyliv]

So examine why left join is not working, Probably returned data has time and it needs to be truncated.

[ingezone]

You were absolutely right, I made the change and it works correctly,

Thank you........... :👍