DBContext.Database.SqlQuery<string>(sql) get a wrong result

[jack-liew]

#DBContext.Database.SqlQuery(sql) get a wrong result

1. Description

I have two database tables have not any association:
msystems

id	name
1	sys1
2	sys2
3	sys3
4	sys4

flows

id	name
1	flow1
2	flow2
3	flow3
4	flow4

---

Now, I need get a one line result set like:

msystem	flow
[{"id":1,"name":"sys1"},{"id":3,"name":"sys3"} ]	[{"id":2,"name":"flow2"},{"id":4,"name":"flow4"}]

When I write a sql like:

SELECT 
    (SELECT CONCAT('[', GROUP_CONCAT(JSON_OBJECT('id', id,'name',NAME)),']') FROM msystems WHERE id IN (1,3)) AS msystem ,
    (SELECT CONCAT('[', GROUP_CONCAT(JSON_OBJECT('id', id,'name',NAME)),']') FROM flows WHERE id IN (2,4)) AS flow
FROM msystems CROSS JOIN flows
LIMIT 1

It worked, and the result be I needed. But when I write it in C#:

    FormattableString sql = $"SELECT (SELECT CONCAT('[', GROUP_CONCAT(JSON_OBJECT('id', id,'name',NAME)),']') FROM msystems WHERE id IN (1,3)) AS msystem, (SELECT CONCAT('[', GROUP_CONCAT(JSON_OBJECT('id', id,'name',NAME)),']') FROM flows WHERE id IN (2,4)) AS flow FROM msystems CROSS JOIN flows LIMIT 1";
    var result = dl.Database.SqlQuery<string>(sql);

I got a wrong reslut:

    [{"id": 3, "name": "sys3"},{"id": 1, "name": "sys1"}]

Where is the column "flow"?

Thanks for great work!

2. Include provider and version information

EF Core version: 6
Database provider: (e.g. MariaDB)
Target framework: (e.g. .NET 7.0)
Operating system: Windows 11
IDE: (e.g. Visual Studio Code 1.84.2)

[roji]

@jack-liew your question doesn't seem related to EF, but more to the SQL you're sending to MariaDB; in other words, if you send that SQL directly to MariaDB (without EF), you should see the same result.

[jack-liew]

@jack-liew your question doesn't seem related to EF, but more to the SQL you're sending to MariaDB; in other words, if you send that SQL directly to MariaDB (without EF), you should see the same result.

I got a right result by sql. maybe it's not mariaDB's problem.

[roji]

With SqlQuery<string>(sql), you're simply providing a SQL for EF to send to the database (EF doesn't do anything to it, it just sends it as-is), and then reading the results back as strings; I really don't know how EF could be changing the contents of the results in the way that you're indicating...

Can you please double-check that you're sending the same SQL with EF and outside EF? If you can't figure it out, then we'll need a minimal, runnable code sample (console program) that shows the problem in order to investigate.

[jack-liew]

With SqlQuery<string>(sql), you're simply providing a SQL for EF to send to the database (EF doesn't do anything to it, it just ……

execute a sql in HeidiSql(a database client):

c# use EFCore:

Thanks a lot.

[roji]

I'm sorry, but I can't investigate anything based on these two screenshots. As I wrote above, in order to help I need a minimal, runnable code sample (console program).

[Arjan321]

Your query returns two columns. You ask EF for a single string column. EF cannot put two columns into a single string.

Use a class or a record to encode your two columns:

var output = context.Database.SqlQuery<Result>(sql).ToList();
record Result(string msystem, string flow);

[roji]

(thanks @Arjan321, I missed that the query was returning two things!)

[jack-liew]

(thanks @Arjan321, I missed that the query was returning two things!)

The return result shold be one record with two column, not two record,but ef.database.sqlquery only viewed one record has one column.That's I'm confused about.

[roji]

You're using SqlQuery<string>(...), which means you expect a single string column from the database, but the actual SQL you're passing projects two string columns.

I'm going to close this as the question has been answered, but if you need further guidance feel free to post back here and we'll help.