mattwar / iqtoolkit Goto Github PK

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

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?

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

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]

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

• CollectionOfY
• • Z

and
X

• CollectionOfY
• • CollectionOfZ
    and

X

• Z
• Y

• -   V
  

• -   U
  

But what isn't working is:

X

• CollectionOfY
• • Z
• • V

or

X

• CollectionOfY
• • CollectionOfZ
• • V

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

ChangesForTest.zip

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?

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

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

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

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 (?):

1. parameter "entity" is not set (null) in EntitySession.OnEntityMaterialized
   -> which will cause the exception later in EntitySession.GetTable
2. parameter "instance" is not an object but an KeyValuePair<int, object> in EntitySession.OnEntityMaterialized

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,

Is there any way to use IQToolkit to translate Expression Tree to SQL without any provider?

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:

• Write my own provider
• Use IQToolkit
• Use Re-Linq toolkit

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.

Any chance you will release this built for .net standard?

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

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