How to use union statement on entity framework core instead of using statement on sql server ?

Question

I work on entity framework core 7 . I face issue I can't do or use union on entity framework core instead of use union on sql server

so i have view contain or return collect of unions as below

CREATE VIEW [dbo].[vDsales]
AS
SELECT        sldate, id, sdate, till, cashier, trnno, brcode, itemno, qty, price, amt, itemflag, stime
FROM            NewSaleNAV.dbo.dsales WITH (NOLOCK)
WHERE        sldate >= '2023-01-01'

 

UNION
SELECT        sldate, id, sdate, till, cashier, trnno, brcode, itemno, qty, price, amt, itemflag
FROM            NewSaleNAV01.dbo.dsales WITH (NOLOCK)
WHERE        sldate >= '2023-01-01'

 


UNION
SELECT        sldate, id, sdate, till, cashier, trnno, brcode, itemno, qty
FROM            NewSaleNAV_Arc22.dbo.dsales WITH (NOLOCK)
WHERE        sldate >= '2022-01-01'

 


UNION
SELECT        sldate, id, sdate, till, cashier, trnno, brcode, itemno, qty
FROM            NewSaleNAV01_Arc22.dbo.dsales WITH (NOLOCK)
WHERE        sldate >= '2022-01-01'

How to convert multi statement union to entity framework core by write statement above with LINQ to sql

my class will return data as below

public class vDsales
{
    public DateTime sldate { get; set; }
    public int id { get; set; }
    public DateTime sdate { get; set; }
    public int till { get; set; }
    public int cashier { get; set; }
    public int trnno { get; set; }
    public int brcode { get; set; }
    public int itemno { get; set; }
    public int qty { get; set; }
    public decimal price { get; set; }
    public decimal amt { get; set; }
    public int itemflag { get; set; }
    public int stime { get; set; }
}

Answer 1

Hi @Ahmed Abd El Aziz

When using LINQ, there has a Union method, you can use it to produce the set union of two sequences. Refer to the following code:

 var result1 = _context.Dsales.Where(c => c.id <3).Union(_context.Dsales.Where(c => c.id>4)).ToList();

The result as below:

Note: in my sample, I'm using the same database and table, from your code, it seems that you are using multiple database, if that is the case, you might need to use the select clause to convert to the data type to vDsalesfirst (Code like this: _context.Dsales.Where(c => c.id <3).Select(c=> new vDsales(){ ...})), then use the Union method.

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best regards,

Dillion