First query OK

Second throw this

{"  Expected: No Exception to be thrown
  But was:   (SQL error occured.
SQL error details 'Type: SyntaxError;'
Query 'DELETE FROM [dbo].[#Tmp_b75b7f93-c472-4304-8484-96a4a29a019d];'
Original message 'Invalid object name '#Tmp_b75b7f93-c472-4304-8484-96a4a29a019d'.')
   at Xtensive.Orm.Providers.StorageDriver.ExecuteCommand[TResult](Session session, DbCommand command, Func`2 action)
   at Xtensive.Orm.Providers.StorageDriver.ExecuteNonQuery(Session session, DbCommand command)
   at Xtensive.Orm.Providers.Command.ExecuteNonQuery()
   at Xtensive.Orm.Providers.BatchingCommandProcessor.ExecuteBatch(Int32 numberOfTasks, QueryRequest lastRequest)
   at Xtensive.Orm.Providers.BatchingCommandProcessor.ExecuteTasks(Boolean allowPartialExecution)
   at Xtensive.Orm.Providers.SqlSessionHandler.Xtensive.Orm.Providers.IProviderExecutor.Clear(IPersistDescriptor descriptor)
   at Xtensive.Orm.Providers.SqlTemporaryDataProvider.ClearAndUnlock(EnumerationContext context)
   at Xtensive.Orm.Providers.SqlIncludeProvider.OnAfterEnumerate(EnumerationContext context)
   at Xtensive.Orm.Rse.Providers.ExecutableProvider.OnAfterEnumerate(EnumerationContext context)
   at Xtensive.Orm.Rse.Providers.ExecutableProvider.OnAfterEnumerate(EnumerationContext context)
   at Xtensive.Orm.Rse.Providers.ExecutableProvider.<GetEnumerator>d__0.System.IDisposable.Dispose()
   at Xtensive.Orm.Rse.Providers.ExecutableProvider.<GetEnumerator>d__0.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Xtensive.Orm.Rse.RecordSet.<GetGreedyEnumerator>d__0.MoveNext()
   at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
   at Xtensive.Core.EnumerableExtensions.<Batch>d__e`1.MoveNext()
   at Xtensive.Core.EnumerableExtensions.<ApplyBeforeAndAfter>d__16`1.MoveNext()
   at System.Linq.Enumerable.<SelectManyIterator>d__16`2.MoveNext()
   at System.Linq.Enumerable.First[TSource](IEnumerable`1 source)
   at lambda_method(Closure , Object[] , IEnumerable`1 , Session , Dictionary`2 , ParameterContext )
   at Xtensive.Core.DelegateBindExtensions.<>c__DisplayClassd`6.<Bind>b__c(T2 arg2, T3 arg3, T4 arg4, T5 arg5)
   at Xtensive.Orm.Linq.TranslatedQuery`1.Execute(Session session, ParameterContext parameterContext)
   at Xtensive.Orm.Linq.QueryProvider.Execute[TResult](Expression expression)
   at System.Linq.Queryable.Count[TSource](IQueryable`1 source)
   at Sample.Program.<>c__DisplayClass0_3.<Main>b__5() in D:\Projects\CleanDO\Sample\Program.cs:line 49
   at NUnit.Framework.Constraints.VoidInvocationDescriptor.Invoke()
   at NUnit.Framework.Constraints.ExceptionInterceptor.Intercept(Object invocation)
"}

Third throw exception that variables count > 2100

Program:

namespace Sample
{
    using System;
    using System.Linq;
    using Xtensive.Orm;
    using Xtensive.Orm.Configuration;
    using NUnit.Framework;

    internal class Program
    {
        private static void Main(string[] args)
        {
            var dc = new DomainConfiguration("sqlserver", @"Data Source=localhost\MSSQLSERVER2016; Initial Catalog=DO40-Tests; Integrated Security=True;")
            {
                UpgradeMode = DomainUpgradeMode.Recreate
            };

            dc.Types.Register(typeof(Program).Assembly);

            using (var d = Domain.Build(dc))
            {
                ExecuteInNewSession(d,
                    () =>
                    {
                        var query = Session.Current.Query.All<TestEntity>();

                        for (var i = 0; i < 12; i++)
                        {
                            var guids = Enumerable.Range(0, 300).Select(e => Guid.NewGuid()).ToArray();

                            query = query.Where(e => e.Id.In(guids));
                        }

                        // OK
                        Assert.DoesNotThrow(() => query.Count());
                    });

                ExecuteInNewSession(d,
                    () =>
                    {
                        var query = Session.Current.Query.All<TestEntity>();

                        for (var i = 0; i < 50; i++)
                        {
                            var guids = Enumerable.Range(0, 300).Select(e => Guid.NewGuid()).ToArray();

                            query = query.Where(e => e.Id.In(guids));
                        }

                        // FAIL
                        Assert.DoesNotThrow(() => query.Count());
                    });

                ExecuteInNewSession(d,
                    () =>
                    {
                        var query = Session.Current.Query.All<TestEntity>();

                        for (var i = 0; i < 50; i++)
                        {
                            var guids = Enumerable.Range(0, 50).Select(e => Guid.NewGuid()).ToArray();

                            query = query.Where(e => e.Id.In(guids));
                        }

                        // FAIL
                        Assert.DoesNotThrow(() => query.Count());
                    });
            }
        }

        private static void ExecuteInNewSession(Domain d, Action action)
        {
            using (var s = d.OpenSession())
            using (s.Activate())
            using (var t = s.OpenTransaction())
            {
                action.Invoke();
                t.Complete();
            }
        }
    }

    [HierarchyRoot]
    public class TestEntity : Entity
    {
        [Field, Key]
        public Guid Id { get; set; }

        [Field]
        public string Name { get; set; }
    }
}

asked Dec 02 '16 at 06:44

Anton%20Guschin's gravatar image

Anton Guschin
73303035

edited Dec 02 '16 at 06:44


4 Answers:

Hi Anton,

Have you tried using Query.Store<guid>() ?

Example :

IQueryable<long> idsTable = Query.Store<long>(idsList);
    var query = from i in Query.All<MyEntity>()
    join id in idsTable on i.Id equals id
    select i;

Regards,

Benoit

answered Dec 02 '16 at 06:54

Benoit%20Nesme's gravatar image

Benoit Nesme
43202024

edited Dec 02 '16 at 06:56

With Query.Store got exception 'Invalid object name '#Tmp_...'

answered Dec 02 '16 at 07:19

Anton%20Guschin's gravatar image

Anton Guschin
73303035

Hello Anton

For the second query, I will check what happens with our TemporaryTableManager, because it seems to be normal case of usage.

For the third query, your 50 where clause with 'In' operations cause the error. The 'In' operations has a parameter allows you to set how to treat elements of given collection. By default it has IncludeAlgorithm.Auto. That means each 'In' operation uses complex condition using query parameters for collections with less than 257, otherwise, it uses temporary table. Each in 'In' do it separately, so if you create 50 'In's with 50 elements in each given collection DO will use complex condition for all of them and you will have exception that variables count > 2100.

My recommendation here is to prepare all ids you need before use them in a query. It would be better for both DO query translation and SQL query performance.

answered Dec 05 '16 at 09:00

Alexey%20Kulakov's gravatar image

Alexey Kulakov
77225

Hello

In our project Where expressions can be inherited and can be used in GUI For example, we have more than one filters with In method User can combine this filters and create new filters, then use this new filters and create more new filters, and so on, therefore we can't gurantee that filters will be beauty and simple

I agree preparation is good thing, but conditions may occure from different sources Seem to me that we can create QueryPreprocessor with custom Visitor and this will be the solution in general situation

It is not dramatic bug, but maybe DO should do that, because can do that :)

answered Jan 16 '17 at 02:24

Anton%20Guschin's gravatar image

Anton Guschin
73303035

Your answer
Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!
toggle preview

powered by OSQA