mattwar / iqtoolkit Goto Github PK
View Code? Open in Web Editor NEWA Toolkit for building LINQ IQueryable providers. This is the official move of my IQToolkit project from CodePlex.
License: Other
A Toolkit for building LINQ IQueryable providers. This is the official move of my IQToolkit project from CodePlex.
License: Other
Hello,
consider this query:
db.OrderDetails.Select(i => new { Date = i.ProductID > 10 ? i.ProductID : 0 });
LinqToSql translates it to a subquery with case statement but iqToolkit queries all OrderDetails and executes the projection expression at client.
This behavior has caused problem when using group by:
var q1 = db.OrderDetails.GroupBy(i => i.ProductID > 10 ? i.ProductID : 0).Select(i => new { i.Key, Count = i.Count() });
var q2 = db.OrderDetails.Select(i => new { key = i.ProductID > 10 ? i.ProductID : 0, i }).GroupBy(i => i.key).Select(i => new { i.Key, Count = i.Count() });
both above queries returns invalid results. what is the workaround?
Query text by iqtoolkit:
SELECT t0.[ProductID], COUNT(*) AS [agg1]
FROM [Order Details] AS t0
GROUP BY t0.[ProductID]
Expected sql query (as linqToSql generates):
SELECT COUNT(*) AS [Count], [t1].[value] AS [Date]
FROM (
SELECT
(CASE
WHEN [t0].[ProductID] > @p0 THEN [t0].[ProductID]
ELSE @p1
END) AS [value]
FROM [dbo].[Order Details] AS [t0]
) AS [t1]
GROUP BY [t1].[value]
Hi,
Is there any way to use IQToolkit
to translate Expression Tree
to SQL
without any provider?
Hello,
shouldn't null values be ignored in an insert command?
If you ignore them, the default values of database will be set.
I managed this in overwriting in a custom SQLformatter:
bool HasNullValue(Expression exp)
{
if (exp.NodeType != ExpressionType.Constant) return false;
var constexp = exp as ConstantExpression;
if (constexp == null) return false;
return constexp.Value == null;
}
protected override Expression VisitInsert(InsertCommand insert)
{
this.Write("INSERT INTO ");
this.WriteTableName(insert.Table.Name);
this.Write("(");
int ct = 0;
for (int i = 0, n = insert.Assignments.Count; i < n; i++)
{
ColumnAssignment ca = insert.Assignments[i];
if (ct > 0) this.Write(", ");
if (!HasNullValue(ca.Expression))
{
this.WriteColumnName(ca.Column.Name);
ct++;
}
}
this.Write(")");
this.WriteLine(Indentation.Same);
this.Write("VALUES (");
ct = 0;
for (int i = 0, n = insert.Assignments.Count; i < n; i++)
{
ColumnAssignment ca = insert.Assignments[i];
if (ct > 0) this.Write(", ");
if (!HasNullValue(ca.Expression))
{
this.Visit(ca.Expression);
ct++;
}
}
this.Write(")");
return insert;
}
Hi Matt,
i think there is an issue in the MastRecentlyUsedCache class in method Lookup.
The method is always returing the last query not the one it found:
for (int i = 0, n = this.list.Count; i < n; i++)
{
cached = this.list[i];
if (fnEquals(cached, item))
{
cacheIndex = 0;
}
}
I think this should be correct:
for (int i = 0, n = list.Count; i < n && cacheIndex < 0; i++)
{
cached = this.list[i];
if (_fnEquals(cached, item))
{
cacheIndex = i;
}
}
The for-loop is two times in that method.
Thanks and best regards,
Markus
First thank for taking time to write this big and very interesting project. this is a "always-want-to-do-it-but-don't-have-a-clue-where-to-start-and-i-am-too-lazy-anyway" kind of project.
When reading and testing your code, I noticed some test did not pass because my thread has a french culture
The error is simple, so is the fix, we just need to replace the following code
(in file SqlFormatter.cs , in namespace IQToolkit.Data.Common)
switch (Type.GetTypeCode(value.GetType()))
{
case TypeCode.Boolean:
this.Write(((bool)value) ? 1 : 0);
break;
case TypeCode.String:
this.Write("'");
this.Write(value);
this.Write("'");
break;
case TypeCode.Object:
throw new NotSupportedException(string.Format("The constant for '{0}' is not supported", value));
case TypeCode.Single:
case TypeCode.Double:
string str = value.ToString();
if (!str.Contains('.'))
{
str += ".0";
}
this.Write(str);
break;
default:
this.Write(value);
break;
}
by this
switch (Type.GetTypeCode(value.GetType()))
{
case TypeCode.Boolean:
this.Write(((bool)value) ? 1 : 0);
break;
case TypeCode.String:
this.Write("'");
this.Write(value);
this.Write("'");
break;
case TypeCode.Object:
throw new NotSupportedException(string.Format("The constant for '{0}' is not supported", value));
case TypeCode.Single:
case TypeCode.Double:
string str = ((IConvertible)value).ToString(CultureInfo.InvariantCulture);
if (!str.Contains('.'))
{
str += ".0";
}
this.Write(str);
break;
default:
var convertible = value as IConvertible;
if (convertible != null)
this.Write(convertible.ToString(CultureInfo.InvariantCulture));
else
this.Write(value);
break;
}
Any chance you will release this built for .net standard?
Based on Test Database (Northwind), Consider we are trying to query Order Details
table to get related records to an specific customer:
Note: I declared one missed association field Order
in Entity OrderDetails
(that already exists in database) before writing this test
public void TestOrderDetailsByCustomer()
{
var q = from d in db.OrderDetails
where d.Order.Customer.CustomerID == "VINET"
select d;
OrderDetail[] so = q.ToArray();
...
}
Excuting this query will fail with Exception:
The LINQ expression node of type 1017 is not supported
This is one of common circumstances in some kind of Apps (with one-to-one relation, used to expand fields into another table, etc) same as what already exits in Northwind Database.
Is there a mistake or bug?
Hi,
i think a combination of Distinct and a case expression is not working like expected.
Example:
var query = db.Customers.Select(r => r.CustomerID == "ALFKI" ? 1 : 0).Distinct();
var test = query.ToList();
Assert.Equal(2, test.Count());
The sql query includes the column CustomerID and the case is evaluated locally.
This results in more than 2 rows.
Thanks...
Markus
Hi Matt,
thanks for the last fix but i have another problem. I created a test case for that issue:
Nullable<Boolean> hasOrders = null;
var query = db.Customers.Select(r => new
{
CustomerID = r.CustomerID,
HasOrders = hasOrders != null
? (bool)hasOrders
: db.Orders.Any(o => o.CustomerID.Equals(r.CustomerID))
});
var test = query.ToList();
At some point in the PartialEvaluator the first case of the mini-if is evaluated and this leads to an invalid cast because hasOrders is null.
Please ignore the proper sense of the query it is only a simplified test case :-)
Thanks and best regards,
Markus
Fascinating work that I would like to leverage with Dapper as ORM.
Has anyone any hint about where or how I could the SQL generation part from the execution part and plug in Dapper as ORM?
Regards, Martin
Dear Matt,
great work! Iโm currently evaluating IQToolkit to access MS Access databases. I have download and build the latest version successfully but run into the following issues:
when I am using a session with policy to query details i get an exception in EntitySession.GetTable!
Here are some details:
i have written this models:
[Table(Name = "Tests")]
public class Test : ViewModelBase
{
[Column(IsPrimaryKey = true, IsGenerated = true)]
public int TestId { get; set; }
public string Description { get; set; }
[Association(KeyMembers = nameof(TestId), RelatedKeyMembers = nameof(TestDetail.TestId))]
public List<TestDetail> TestDetails { get; set; } = new List<TestDetail>();
}
[Table(Name = "TestDetails")]
public class TestDetail : ViewModelBase
{
[Column(IsPrimaryKey = true, IsGenerated = true)]
public int TestDetailId { get; set; }
public int TestId { get; set; }
public string TestDetailDescription { get; set; }
[Association(Member = nameof(TestDetail.Test), KeyMembers = nameof(TestId), RelatedKeyMembers = "TestId")]
public Test Test { get; set; }
}
Test case 1: Query Tests + TestDetails with policy
"policy.IncludeWith(t => t.TestDetails)"
static void TestProviderWithPolicy()
{
var policy = new EntityPolicy();
policy.IncludeWith<Test>(t => t.TestDetails);
var provider = new AccessQueryProvider("C:\\Users\\z271460\\Documents\\Tests.accdb").WithPolicy(policy);
/// Query Tests + TestDetails
/// -> I get all dependent TestDetail records but TestDetail.Test (parent) is null (not set)
///
var result = provider.GetTable<Test>().ToList();
}
I get all dependent TestDetail records but TestDetail.Test (parent) is null (not set)
Test case 2: Query Tests + TestDetails with policies
"policy.IncludeWith(t => t.TestDetails);" and
"policy.IncludeWith(t => t.Test);"
static void TestProviderWithPolicy2()
{
var policy = new EntityPolicy();
policy.IncludeWith<Test>(t => t.TestDetails);
policy.IncludeWith<TestDetail>(t => t.Test);
var provider = new AccessQueryProvider("C:\\Users\\z271460\\Documents\\Tests.accdb").WithPolicy(policy);
/// Query Tests + TestDetails
/// -> I get all dependent TestDetail records and TestDetail.Test (parent) is set.
/// But TestDetail.Test.TestDetails is not set.
/// So i think "recursions" wont work correctly and TestDetail.Test is a new instance of Test?
///
var result = provider.GetTable<Test>().ToList();
}
I get all dependent TestDetail records and TestDetail.Test (parent) is set.
But TestDetail.Test.TestDetails is not set.
So i think "recursions" wont work correctly and TestDetail.Test is a new instance of Test?
Test case 3: Session without policies
static void TestSessionWithoutPolicy()
{
var provider = new AccessQueryProvider("C:\\Users\\z271460\\Documents\\Tests.accdb");
var session = new EntitySession(provider);
/// i get all Tests without TestDetails as expected -> ok
///
var result = session.GetTable<Test>().ToList();
}
Works as excpected!
Test case 4: Session with policy -> causes an exception
static void TestSessionWithPolicy()
{
var policy = new EntityPolicy();
policy.IncludeWith<Test>(t => t.TestDetails);
var provider = new AccessQueryProvider("C:\\Users\\z271460\\Documents\\Tests.accdb").WithPolicy(policy);
var session = new EntitySession(provider);
/// i get an exception in EntitySession.GetTable
///
var result = session.GetTable<Test>().ToList();
}
I think there are 2 problems causing this exception (?):
I have tried many workarounds to avoid this exception and get sessions with policies working!
But without success :-( ...
Could you please help?
Just curious, what is this line supposed to do?
Hi,
I'm currently delving into LINQ query providers using custom ExpressionVisitors to be able translate linq expression-trees into specific dsl-queries or to override/optimize some provider's by extending them.
I saw you updated your project a while ago but the doc seems to be way outdated.
Could you please update it or provide some use-case examples ?
Especially dealing with EntityFramework (6 currently), I wish to ask you if you have some pointer to inject such query providers directly into a subclasses of DbContext, the native API being quite restricted by internal use
Thanks in advance for your answer and best wishes for the upcoming year,
Max.
I apologize for using this method for writing to you a request of information, but unfortunately I found no other reference.
I'm developing a project named DeveelDB, a SQL-99 database system for .NET: I recently rebooted the whole project, that was quite old indeed, but not well-written.
In this perspective I'm writing, among others, a LINQ interface to it and I'm pondering which option to follow:
I explored the source code of IQToolkit and Re-Linq and I must say that your code looks quite clean and straight forward when compared to Re-Linq.
On the other side, Re-Linq seems to be used in real world business cases (NHibernate and EF7 above all), while IQToolkit seems ignored.
I wish to know what is the confidence you have on complex queries and mappings (eg. joins, projections, sub-queries, etc)? What is the code coverage?
When I see the projects for SQL Server, MySQL and other examples, I think it would be very simple to start writing my provider following those examples, but I also want to provide a professional project to be used (thing that I haven't done for years) in complex and challenging contexts.
Thank you for all your time.
public class BaseClass
{
public int ID { get; set; }
}
public class SubClass : BaseClass
{ }
var param = Expression.Parameter(typeof(SubClass));
var body = Expression.Property(param, "ID");
var lambda = Expression.Lambda<Func<SubClass, int>>(body, param);
Expression<Func<SubClass, int>> lambdaExpr = x => x.ID;
Console.WriteLine(ExpressionComparer.AreEqual(lambda, lambdaExpr));
These 2 lambdas should be equal, but ExpressionComparer.AreEqual() returns false.
This issue should be related to this question:
https://stackoverflow.com/questions/13615927/equality-for-net-propertyinfos#13616025
Hello Matthew and other contributors,
I'm Tom Wouters, a senior architect working on a solution based on iqtoolkit for advanced mapping.
We currently use iqtoolkit as our core engine and we're really happy about it, but I'm faced with a very specific issue.
When we create a request for a table with a collection and in that collection 2 sub items (trigger is more than one sub relation), the program gives a column not in scope error.
To my opinion it should be possible with iqtoolkit, because this situation works in the root (a table with more than one sub query), only the scope either gets build incorrectly
when a one to many memberbinding is constructued, or the construction of a one to many memberbinding itself is called incorrectly.
So basically working:
X
and
X
X
- V
- U
But what isn't working is:
X
or
X
As an example I modified the current Orders to have a direct link to a product (yes I know, it's completely illogical, but it gets the trick done), updated the northwind.db3 database to reflect the changes (all orders are connected to product 1)
changes:
Test.Common\Northwind.cs
From
public class Order
{
public int OrderID;
public string CustomerID;
public DateTime OrderDate;
public Customer Customer;
public List<OrderDetail> Details;
}
To
public class Order
{
public int OrderID;
public string CustomerID;
public DateTime OrderDate;
public Customer Customer;
public List<OrderDetail> Details;
public int ProductID { get; set; } //*new*
public Product Product; //*new*
}
From
[Table]
[Column(Member = "OrderID", IsPrimaryKey = true, IsGenerated = true)]
[Column(Member = "CustomerID")]
[Column(Member = "OrderDate")]
[Association(Member = "Customer", KeyMembers = "CustomerID", RelatedEntityID = "Customers", RelatedKeyMembers = "CustomerID")]
[Association(Member = "Details", KeyMembers = "OrderID", RelatedEntityID = "OrderDetails", RelatedKeyMembers = "OrderID")]
public override IEntityTable<Order> Orders
{
get { return base.Orders; }
}
To
[Table]
[Column(Member = "OrderID", IsPrimaryKey = true, IsGenerated = true)]
[Column(Member = "CustomerID")]
[Column(Member = "OrderDate")]
[Column(Member = "ProductId")] //*new*
[Association(Member = "Customer", KeyMembers = "CustomerID", RelatedEntityID = "Customers", RelatedKeyMembers = "CustomerID")]
[Association(Member = "Details", KeyMembers = "OrderID", RelatedEntityID = "OrderDetails", RelatedKeyMembers = "OrderID")]
[Association(Member = "Product", KeyMembers = "ProductID", RelatedEntityID = "Products", RelatedKeyMembers = "ID")] //*new*
public override IEntityTable<Order> Orders
{
get { return base.Orders; }
}
And in Test.Common\NorthwindExecutionTests.cs
I added the test:
public void TestCustomersIncludeOrdersAndDetailsAndCustomer()
{
var policy = new EntityPolicy();
policy.IncludeWith<Customer>(c => c.Orders);
policy.IncludeWith<Order>(o => o.Details);
policy.IncludeWith<Order>(o => o.Product);
Northwind nw = new Northwind(this.GetProvider().New(policy));
var custs = nw.Customers.Where(c => c.CustomerID == "ALFKI").ToList();
Assert.Equal(1, custs.Count);
Assert.NotEqual(null, custs[0].Orders);
Assert.Equal(6, custs[0].Orders.Count);
Assert.Equal(true, custs[0].Orders.Any(o => o.OrderID == 10643));
Assert.NotEqual(null, custs[0].Orders.Single(o => o.OrderID == 10643).Details);
Assert.Equal(3, custs[0].Orders.Single(o => o.OrderID == 10643).Details.Count);
}
As test engine, I used the Test.Sqlite because we're mainly using a sqlite db. In order to have test.sqlite run, I added a reference to IQToolkit.Data.SQLite
and in the main call of program.cs, I added var _x = typeof(SQLiteFormatter); (so that the sqlite assembly is loaded)
Could you guys give me a pointer on where the issue is located? Or maybe even solve it?
I spent more then a week narrowing the issue down, and trying to find a logical place on where to fix it, but I really can't seem to wrap my head around it.
The zip file contains the northwind.db3 changed db, the NorthwindExecutionTests.cs and Northwind.cs
I remember reading these post a long time ago and I found them to be beneficial for understanding the inner workings of this project. I think it would be nice to have this content either copied to GitHub, or linked to from this repository. I would lean toward centralizing the content on GitHub but that's just my preference.
https://blogs.msdn.microsoft.com/mattwar/2008/11/18/linq-building-an-iqueryable-provider-series/
Part I - Reusable IQueryable base classes
Part II - Where and reusable Expression tree visitor
Part III - Local variable references
Part IV - Select
Part V - Improved Column binding
Part VI - Nested queries
Part VII - Join and SelectMany
Part VIII - OrderBy
Part IX - Removing redundant subqueries
Part X โ GroupBy and Aggregates
Part XI - More of everything
Part XII - Relationships and the IQ Toolkit
Part XIII - Updates and Batch Processing
Part XIV - Mapping and Providers
Part XV - Transactions, Sessions and Factories
Part XVI - Performance and Caching
Part XVII - Automatic Caching and Loading Policies
I cannot find the NuGet package for IQToolkit 2.0, can you please create one?
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.