Question 87271 in ASP.NET Question posted in
The official documentation can be found here.

Cross Apply Values (1, columnName) as (code, display) in LINQ – Asp.net

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:
Teh result of the above query(SQL)

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

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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);

  2. 0

    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
     };
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search