Why my sql query works on mssql and doesn't work on visual studio?

Question

My sql query which is:

Select islemtipi, sum(AlcT), sum(BrcT), sum(KdvTop), sum(iskonto) from carih Where FirmaKodu ='001' and islemtipi = 'Satış Faturası' or islemtipi ='Alış Faturası' or islemtipi = 'Tahsilat' or islemtipi = 'Ödeme' and (Tarih>= '2019-01-01' and Tarih <='2019-12-31') group by islemtipi

Works on MSSQL perfectly. But when I tried same query on Visual Studio, it gave me only 2 column result which are Tahsilat and Ödeme columns. I could't solve the problem. Maybe problem is about my query contains Turkish Language Characters but also Ödeme contains turkish character which is Ö.

Also my c# code is

using (SistemEntities db = new SistemEntities())
                {
string sorgu = "Select islemtipi, sum(AlcT), sum(BrcT), sum(KdvTop), sum(iskonto) from carih Where FirmaKodu ='001'
    and islemtipi = 'Satış Faturası' or islemtipi ='Alış Faturası' or islemtipi = 'Tahsilat'
    or islemtipi = 'Ödeme' and (Tarih>= '2019-01-01'  and Tarih <='2019-12-31')
    group by islemtipi";
                    List<ENT_carih> rapor = db.Database.SqlQuery<ENT_carih>(sorgu).ToList();

                    return rapor;
                }

And it gave me only 2 column result which are Tahsilat and Ödeme columns.

Don't use `SELECT *`, specify your columns, it's better for you and it's faster. — CaldeiraG, Sep 18 '19 at 10:48
Perhaps you ran the query against the wrong instance/database in VS. — Thom A, Sep 18 '19 at 10:51
Could be to do with characters,`Ö` could be fine - as it's also used in German? as @CaldeiraG says, try specifying the columns to see if that works. — Stuart.Sklinar, Sep 18 '19 at 10:51
Okay i specified my columns but still i have same problems select islemtipi, sum(AlcT), sum(BrcT), sum(KdvTop), sum(iskonto) from carih where FirmaKodu ='001' and islemtipi = 'Satış Faturası' or islemtipi ='Alış Faturası' or islemtipi = 'Tahsilat' or islemtipi = 'Ödeme' and (Tarih>= '2019-01-01' and Tarih <='2019-12-31') group by islemtipi — Damla Eryılmaz, Sep 18 '19 at 10:55
Sounds like the problem is your application, not the SQL. You should be posting the C# code, and definition of your datagrid. — Thom A, Sep 18 '19 at 11:01
@DamlaEryılmaz Visual Studio doesn't exequte SQL queries, it sends them to the database. If your code throws it means it has errors. Post your code and the actual errors in the question itself. — Panagiotis Kanavos, Sep 18 '19 at 11:01
I think in the original question OP may be confusing things by saying he is only getting two column results _"which are Tahsilat and Ödeme columns"_ - I think he means rows & the result set is not containing the rows for _"'Satış Faturası' & 'Alış Faturası'"_ both of which have the ş character — PaulF, Sep 18 '19 at 11:02
@DamlaEryılmaz are you using string contatenation to create the query perhaps? This can easily lead to problems due to incorrect quoting, conversion errors, mangled Unicode text etc *and* allows SQL injection attacks. Use parameterized queries instead — Panagiotis Kanavos, Sep 18 '19 at 11:03
Possibly duplicate of this??? https://stackoverflow.com/questions/14483074/cannot-use-turkish-characters-with-entity-framework — PaulF, Sep 18 '19 at 11:03
@PaulF if the code uses string concatenation, same cause, not the best duplicate. — Panagiotis Kanavos, Sep 18 '19 at 11:08
Does it work if you use change the start of each string from `'` to `N'`? — mjwills, Sep 18 '19 at 11:09
@PanagiotisKanavos yes i checked that link and yes it looks like it cant solve my problem easily but i cannot do any changes on db beacuse i don't have initiative. — Damla Eryılmaz, Sep 18 '19 at 11:14
@DamlaEryılmaz this has *nothing to do with the database*. The problem is the code itself. The conclusion in that link is simply wrong and your own post proves this - SO is an ASP.NET site that stores data in nvarchar columns. You wouldn't be able to post non-English text otherwise. — Panagiotis Kanavos, Sep 18 '19 at 11:15
@DamlaEryılmaz you're sending ASCII instead of Unicode string literals in that code. Unicode string literals start with N, ie `N'Alış Faturası'`. Your query forces SQL Server to translate those strings to ASCII, thus losing any characters that don't match the table's collation. DON'T just add N though, use parameterized queries instead — Panagiotis Kanavos, Sep 18 '19 at 11:17
I suspect someone should close this as duplicate of https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection . — mjwills, Sep 18 '19 at 11:20

Panagiotis Kanavos · Answer 1 · 2019-09-18T11:39:08.420

The code uses ASCII instead of Unicode literals for non-English strings. The solution is not to add the N prefix though. The best option is to use parameterized queries.

This looks like EF 6.2 code. Database.SqlQuery accepts parameters. The code should change at least to :

var query= @"Select islemtipi, sum(AlcT), sum(BrcT), sum(KdvTop), sum(iskonto) 
             from carih Where FirmaKodu =@code
             and islemtipi = @item1 or islemtipi =@item2 or islemtipi = @item3
    or islemtipi = @item4 and (Tarih>= @from  and Tarih <=@to)
    group by islemtipi";
var rapor = db.Database.SqlQuery<ENT_carih>(query,
                new SqlParameter("@code","001"),
                new SqlParameter("@item1","Satış Faturası"),
                ...
                new SqlParameter("@from",fromDate.Date),
                new SqlParameter("@to",toDate.Date),
            ).ToList();

EF will generate a proper parameterized query from this and pass the strings as Unicode (nvarchar) parameters. Numbers, dates etc will be passed as strongly-typed parameters too. If the from and to parameters need to be date instead of datetime, one can create a date typed parameter with one of the constructors that accept a data type :

new SqlParameter("@from",SqlDbType.Date) { Value=fromDate.Date}

The query isn't very complex though and could be written using LINQ. Assuming Carih is a DbSet one would write :

var results = db.Carih.Where(c=>c.FirmaKodu=someCode && c.islemtipi=someItem &&....)
                .GroupBy(c=>c.islemtipi)
                .Select(grp=>new { islemtipi=grp.Key,
                                   AlcT=grp.Sum(c=>c.AlcT),
                                   ...
                                 });

BTW, the original query is a bit strange. It filters by FirmaKodu AND islemtipi=.. OR one of the other options. I suspect the correct condition would be :

Where FirmaKodu ='001'
     and islemtipi IN ('Satış Faturası',...)
     and (Tarih>= '2019-01-01'  and Tarih <='2019-12-31')

To generate an IN clause in LINQ, one needs to put all items in a container and use container.Contains(someField) :

var items=new[]{"...","..."};
var results = db.Carih.Where(c=>c.FirmaKodu=someCode
                                && items.Contains(c.islemtipi) 
                                && c.Tarih>=fromDate.Date
                                && c.Tarih<=toDate.Date)
                .GroupBy(c=>c.islemtipi)
                .Select(grp=>new { islemtipi=grp.Key,
                                   AlcT=grp.Sum(c=>c.AlcT),
                                   ...
                                 });

score 0 · Answer 2 · answered Sep 23 '19 at 07:57

0

I solved the problem by typing 'Alış Faturası' instead of 'Alýþ Faturasý' and 'Satış Faturası' instead of 'Satýþ Faturasý'

answered Sep 23 '19 at 07:57

Damla Eryılmaz

1
2

Why my sql query works on mssql and doesn't work on visual studio?

2 Answers2