How to convert SQL query values separated comma to list by using entity framework core 7?

Question

I work on asp.net core razor page . I face issue How to convert complicated sql query to entity framework core 7

Exactly I need to convert this SQL statement to LINQ to entity without write SQL statement on csharp meaning i need to use code first entity framework core 7

function below get values separated comma and convert it to list based on related Print server .

 public DataTable GetBranchesRelatedToServer(string PrintServer)
        {
               DataTable dt = new DataTable();
                
                cmd.CommandText = ";with cte as (SELECT distinct PrintServer,REPLACE (Split.A.value('.', 'VARCHAR(4000)') , ' ', '' ) as BranchId FROM  (SELECT BranchCode, PrintServer,CAST ('<M>' + REPLACE(BranchCode, ',', '</M><M>') + '</M>' AS XML) AS String   FROM  [dbo].[tbl_branchPDFexe]) AS A CROSS APPLY String.nodes ('/M') AS Split(A)) select t.PrintServer,t.BranchId,b.vBranchDesc from  cte t inner join [dbo].[tbl_Branch] b with(nolock) on t.BranchId=b.iBranchCode and PrintServer=@PrintServer";
                
            
            return dt;
        }

models used is

[Table("tbl_branchPDFexe")]
    public class BranchPDFexe
    {
       public decimal Id { get; set; }
       public string BranchCode { get; set; }
       public string PrintServer { get; set; }
    }

[Table("tbl_Branch")]
    public class Branch
    {
        [Key]
        public string iBranchCode { get; set; }
        public string vBranchDesc { get; set; }

    }

Data input before query run on table tbl_branchPDFexe

Table structure tbl_Branch

Expected result will be

what i try as below

but i get error

System.InvalidOperationException: 'The LINQ expression 'b => b.BranchCode.Split(
separator: char[] { , },
options: RemoveEmptyEntries)
.AsQueryable()
     .Distinct()
     .ToList();

public list GetBranchesRelatedToServer(string PrintServer)
            {
 var distinctBranchCodesAndPrintServers = _adc.BranchPDFexe      .SelectMany(b => b.BranchCode.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries)          .Select(bc => new { BranchCode = bc.Trim(), b.PrintServer }))      .Distinct()      .ToList();
// so How to make it by linq to entity 
                   return list
            }

Answer 1

as stated before ef expressions to sql are limited. the string.Split() function is not supported. for list of supported string functions see:

https://learn.microsoft.com/en-us/ef/core/providers/sql-server/functions

as you don't seem to use a where clause, you could fetch the data, then use linq to objects which allows any c# operation. also unless you plan on using reflection, the function should return a concrete type, not an anonymous object.

public List<BranchList> GetBranchesRelatedToServer(string PrintServer)
{
       return  distinctBranchCodesAndPrintServers = _adc.BranchPDFexe
              .ToList()  // execute query and convert to objects
              .SelectMany(b => b.BranchCode.Split(',', StringSplitOptions.RemoveEmptyEntries))
              .Select(bc => new BranchList { BranchCode = bc.Trim(), b.PrintServer }))
              .Distinct()
              .ToList();
}

Answer 2

Hi @Ahmed Abd El Aziz

Based on the expected result and your data table, I use the following query statement to get the same result.

            var printserver = "10";
            var itemlist = _context.BranchPDFexes.ToList().Where(c => c.PrintServer == printserver)
                .SelectMany(c => c.BranchCode.Split(",").AsEnumerable().Select(bc => new BranchViewModel() { PrintServer = c.PrintServer, BranchId = bc }))
                .GroupJoin(_context.Branches, bpdf => bpdf.BranchId, branch => branch.iBranchCode, (bpdf, branch) => new { Bpdf = bpdf, Bra = branch }).DefaultIfEmpty()
                .Select(x=> new BranchViewModel() { PrintServer = x.Bpdf.PrintServer, BranchId = x.Bpdf.BranchId, vBranchDesc = x.Bra.First().vBranchDesc })
                .ToList();

The result as below:

Besides, you can also create a stored procedure on the Database, like this:

CREATE PROCEDURE [dbo].[GetBranchesRelatedToServer]
            @PrintServer NVARCHAR(Max)
        AS
        BEGIN
            ;with cte as (
            SELECT distinct PrintServer,
            REPLACE (Split.A.value('.', 'VARCHAR(4000)') , ' ', '' ) as BranchId 
            FROM  (SELECT BranchCode, PrintServer, 
            CAST ('<M>' + REPLACE(BranchCode, ',', '</M><M>') + '</M>' AS XML) AS String   
            FROM  [dbo].[tbl_branchPDFexe]) AS A CROSS APPLY String.nodes ('/M') AS Split(A)) 
            select t.PrintServer,t.BranchId,b.vBranchDesc from  
            cte t inner join [dbo].[tbl_Branch] b with(nolock) 
            on t.BranchId=b.iBranchCode and PrintServer=@PrintServer
        END
GO

Then based on the return data to create model:

    public class BranchViewModel
    { 
        public string PrintServer { get; set; }

        [Key]
        
        public string BranchId { get; set; }
        public string vBranchDesc { get; set; }
    }

After that, add the DbSet in the DbContext and enable migration to generate data table in the database using the following commands:

add-migration addbranchviewmodel
update-database

Then, in the Controller, you can use the FromSql() method to execute the sql query statement to consume the stored procedure, like this:

var items = _context.BranchViewModels.FromSql($"Execute GetBranchesRelatedToServer '10'").ToList();

It also gets the same result:

---

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