Cross Apply Values (1, columnName) as (code, display) in LINQ-CodePudding

I have an entity like:

public class TEvent
{
   
        public int? January { get; set; }
        public int? February { get; set; }
        public int? March { get; set; }
        public int? April { get; set; }
        public int? May { get; set; }
        public int? June { get; set; }
        public int? July { get; set; }
        public int? August { get; set; }
        public int? September { get; set; }
        public int? October { get; set; }
        public int? November { get; set; }
        public int? December { get; set; }
        //and much more
}

What I want my LINQ code to achieve:

SELECT MonthCode, * FROM T_Events
CROSS APPLY (VALUES (1, January), (2, February), (3, March), (4, April), (5, May), (6, June), (7, July), (8, August), (9, September), (10, October), (11, November), (12, December)) AS CA(MonthCode, Display)
WHERE Display = -1

The problem is that I don't know how to have LINQ know it is a column name.

What I've tried

var dd = new List<object>()
{
    new {January = 1 },
    new {February = 2},
    new {March = 3},
    new {April = 4},
    new { May = 5},
    new {June = 6},
    new {July = 7},
    new {Augest = 8},
    new {September = 9},
    new {October = 10},
    new {November = 11},
    new {December = 12}
};

var q = 
     from events in _context.TEvents
     from mds in dd

Now when I write mds. I don't get anything because of course it is of type object, but if I don't use object how would I specify custom column names and get the value of 'Display'

Expected result:

The value of Months(Jan, feb etc...) can be (0 or -1). When the Columns are converted to Rows, I get 12 rows, each with it's own MonthCode and display, now let's say for a record March and April are -1, then the 3rd and 4th record will have Display = -1 and the rest will have 0, while all will retain it's month codes

CodePudding user response：

I would suggest to use linq2db.EntityFrameworkCore, note that I'm one of the creators. This extension brings power of linq2db to EF Core projects. And library supports join to local collections.

class MonthDescription
{
    public int MonthCode { get; set; }
    public string Display { get; set; }
}

var months = new []
{
    new MonthDescription { MonthCode = 1,  Display = "January"   },
    new MonthDescription { MonthCode = 2,  Display = "February"  },
    new MonthDescription { MonthCode = 3,  Display = "March"     },
    new MonthDescription { MonthCode = 4,  Display = "April"     },
    new MonthDescription { MonthCode = 5,  Display = "May"       },
    new MonthDescription { MonthCode = 6,  Display = "June"      },
    new MonthDescription { MonthCode = 7,  Display = "July"      },
    new MonthDescription { MonthCode = 8,  Display = "Augest"    },
    new MonthDescription { MonthCode = 9,  Display = "September" },
    new MonthDescription { MonthCode = 10, Display = "October"   },
    new MonthDescription { MonthCode = 11, Display = "November"  },
    new MonthDescription { MonthCode = 12, Display = "December"  }
};

var query = 
     from event in _context.TEvents.ToLinqToDB() // switching LINQ provider
     from md in months
     select new 
     {
         event,
         md
     };

CodePudding user response：

Thanks @Svyatoslav Danyliv For your answer. I've also found another way to achieve what I want through Union.

First Write the main query:

var evAll = from child in _context.TEvents
                        join parent in _context.TEventDomains
                            on child.ID equals parent.ID
                        join p in _context.TPriorities
                            on parent.IDPriority equals p.Idpriority into PJoin
                        from p in PJoin.DefaultIfEmpty()
                        select new TEventExtended(child, p.PriorityCode)

Now I can select for each month and union the answer. like

from ev in evAll
select new TEventExtended(ev, 1, ev.January))
.Union(
from ev in evAll
select new TEventExtended(ev, 2, ev.February))
...etc etc

Here, I created a class/record which extend TEvent by 3 properties i.e. MonthCode, Display, Priority, and created constructors which copies data from the original TEvent/TEventExtended NOTE: If you're using C# 10. You don't need to create constructors, simply use: new TEventExtended() with { MonthCode = 12, Display = ev.December} Finally after adding 12 unions

.Union(
from ev in evAll
select new TEventExtended(ev, 12, ev.December))
.Where(x => x.Display == -1)
.OrderBy(x => x.MonthCode >= request.Month ? x.MonthCode : x.MonthCode   12)
.ThenBy(x => x.PCode)
.Select(x =>
new MyFinalDTO
{
    //projection
}).ToListAsync(cancellationToken);