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

when 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

so code will be after convert it to entity framework core

 public list GetBranchesRelatedToServer(string PrintServer)
            {
dbcontext.BranchPDFexe.where().join(branch)
// so How to make it by linq to entity 
                   return list
            }

updated post

as you say .NET core 7 don't support CTE with xml

can you show to me available ways can do it by using entity framework core 7

updated post can you please help me to apply this logic

1-return list of BranchCode without comma separated from table BranchPDFexe

2-join with table Branch to get branchName so no need to depend on sql

So How to do these steps please

Update Post

can you please help me to solve issue without using sql query as below

var distinctBranchCodesAndPrintServers = _adc.BranchPDFexe
     .SelectMany(b => b.BranchCode.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries)
         .Select(bc => new { BranchCode = bc.Trim(), b.PrintServer }));

but i get error

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

Update Post

sql statment

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

statement above split branch code from table BranchPDFexe to list of

numbers then join with table tbl_branches to get VBranchDesc

so BranchCode have 10206,10207,101lc01 for print server 10 will be

10206   10
10207   10
101lc01 10

and after that join with table tbl_branches to get vbranchDesc so it will be

10206    Jumira           10  
10207    Al Aweer         10
101lc01  MalCamp- Alquoz  10

so final Statement will be

PrinterServer iBranchCode VBranchDesc

You can't. CTE (WITH), AS XML, with(nolock) are not supported by EF Core. — Svyatoslav Danyliv
– Svyatoslav Danyliv, Commented Aug 2, 2023 at 10:07
so how apply this logic on ef core 7 ";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"; — ahmed abdelaziz
– ahmed abdelaziz, Commented Aug 2, 2023 at 10:12
I can repeat again. EF Core's LINQ do not support CTE, AS XML - leave raw SQL or create View. — Svyatoslav Danyliv
– Svyatoslav Danyliv, Commented Aug 2, 2023 at 10:15
ok so How to do it by any way without using ado.net and by use ef core code first — ahmed abdelaziz
– ahmed abdelaziz, Commented Aug 2, 2023 at 10:18

Charlieface · Accepted Answer · 2023-08-02 10:57:24Z

I'll preface by saying: your database design is fundamentally broken. You should normalize the table out into separate rows..

You can use STRING_SPLIT in a raw query to get an IQueryable<string>, which you can then compose over to get the rest of the joins.

public static IQueryable<string> GetSplitBranchPdf(this YourDbContext db, string printServer)
{
    return db.Database.SqlQueryRaw<string>(@"
SELECT
  TRIM(v.value) AS Value
FROM dbo.tbl_branchPDFexe bpd
CROSS APPLY STRING_SPLIT(bpd.BranchCode, ',') v
WHERE bpd.PrintServer = @PrintServer
",
        new SqlParameter("@PrintServer", SqlDbType.NVarChar, 100) { Value = printServer }
    );
}

public async Task<List<MyResult>> GetBranchesRelatedToServer(string printServer)
{
    var splitBranches = Db.GetSplitBranchPdf(printServer);
    // must do this in a separate step, do not merge
    var results =
        from b in Db.Branch
        join t in splitBranches
          on t equals b.iBranchCode
        select new MyResult {
            PrintServer = printServer,
            BranchId = t.BranchId,
            vBranchDesc = b.vBranchDesc
        };
    return results.ToListAsync();
}

For raw queries returning a single scalar column we can use db.Database.SqlQueryRaw, and the column name must be Value. Otherwise we need to use a full entity with db.Set<SomeEntity>.FromSqlRaw.

Note that the first function returns an IQueryable. It does not run any anything until it's actually queried, which means it is composable.

abolfazl sadeghi · Accepted Answer · 2023-08-02 12:02:17Z

You have different ways, one way is to use FromSqlRaw

First, you have to define a class, but you work with the following configuration so that the table is not created

define a class


public class vw_BranchPDFexe
{
    public string PrintServer { get; set; }
    public string BranchId { get; set; }
    public string vBranchDesc { get; set; }
}

configuration so that the table is not created

 protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
base.OnModelCreating(modelBuilder);

        modelBuilder.Ignore<vw_BranchPDFexe>();

        modelBuilder.Entity<vw_BranchPDFexe>().HasNoKey();

Run Query


var _result= 
context.vw_BranchPDFexe.FromSqlRaw(
        $";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}"

).ToList();

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related