Comments (8)
What is wrong with this SQL?
And I am not sure what is IIF and Convert methods.
from linq2db.
Sql Server says:
Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'LIKE'.
В этом запросе ему не нравится вложенное условие like
from linq2db.
What SQL do you expect?
from linq2db.
SELECT
[it].[Name],
[it].[Type] as [Type1],
[it].[Chief_Id],
[it].[Parent_Id],
[it].[Id]
FROM
[Departaments] [it]
WHERE
CASE
WHEN CASE
WHEN [it].[Name] IS NULL THEN NULL
when [it].[Name] LIKE N'КИ%' then 1
END IS NULL
THEN 0
WHEN [it].[Name] IS NULL THEN NULL
when [it].[Name] LIKE N'КИ%' then 1
END = 1
In SQL Server 2012 exists IIF function http://msdn.microsoft.com/en-us/library/hh213574.aspx
from linq2db.
Who implemented conversion of the IIF function to SQL?
from linq2db.
IIF in linq query ConditionalExpression.
Conversion implemented in ExpressionBuilder.SqlBuilder line 710.
from linq2db.
From your example .Where(it => IIF((IIF((it.Name...
.
IIF looks like a custom function. What is that?
from linq2db.
This LINQ query restored from Expression. IIF not function, is text view ConditionalExpression.
Sample:
var par = Expression.Parameter(typeof(Organization), "it");
var testExp = Expression.Equal(Expression.Property(par, "Name"), Expression.Constant(null)); //(it.Name==null)
var calExp=Expression.Convert(Expression.Call(Expression.Property(par, "Name"), typeof(string).GetMethod("StartsWith", new Type[] { typeof(string) }), Expression.Constant("КИ")), typeof(int?)); //Convert(it.Name.StartsWith("КИ"))
Expression conditionExpr = Expression.Condition( //II(
testExp,
Expression.Constant(null, typeof(int?)), //,null
calExp
);
Данный запрос формируется кодом WCF Data Services, поэтому мне пришлось его востанавливать из выражения. Думал что IIF это встроенная функция и не стал ее подменять. Ваши вопросы потребовали более глубокого изучения вопроса. Оказалось что IIF это текстовое представление выражения типа ConditionalExpression, что доказывает код выше.
Если посмотреть в отладчике на conditionExpr то будет написано выражение IIF.
PS. Может имеет смысл такое выражение представлять как функцию IIF, а не CASE как сейчас. А провайдер уже разберется и преобразует в нужный SQL.
from linq2db.
Related Issues (20)
- PostgreSQLBulkCopy performance degradation for method BulkCopyType.ProviderSpecific
- Method not found in target type: Microsoft.Data.Sqlite.SqliteConnection.ClearAllPools() HOT 4
- Linq2Db not all stored procedures scaffolding HOT 3
- Use custom property Getters in Linq queries HOT 5
- Remove `timestamp` "type" special handling in SQLite schema provider HOT 1
- [Association] with QueryExpressionMethod does not honor CanBeNull property HOT 3
- Connection pool depletion when using new DataContext(...), together with LoadWith and FirstOrDefault (or SingleOrDefault) HOT 6
- NotImplementedException when filtering on a sub-query HOT 9
- InheritanceMappingAttribute is working incorrectly when abstract class is present in inheritance tree HOT 5
- BulkCopy failure on CLOB HOT 1
- ConstantExpressions do not get parameterized in SQL statements HOT 1
- [QUESTION] Terminate a query by cancellation token HOT 5
- default-schemas not working
- Firebird different number datatype between constant or variable usage HOT 2
- Task-spawning code refactoring HOT 2
- WithOptions<T> looses CommandTimeout property
- Association property is null, while query is correct HOT 2
- Exception using Converter HOT 1
- Read some json values, Common Language Runtime detected an invalid program. HOT 5
- update documentation to show how to use fluent mapping
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from linq2db.