none
Problem with GetTable and Where clauses RRS feed

  • Question

  • I am having problems with LINQ and GetTable method. I have a class SQLRepository with some generic methods.

    Code Snippet

    public class SQLRepository<T> : IRepository<T>

            where T : class

    {

            protected IDataContextFactory dataContextFactory;

     

            public SQLRepository (IDataContextFactory dataContextFactory)

            {

                this.dataContextFactory = dataContextFactory;

            }

     

      public IEnumerable<T> FindAll(Func<T, bool> exp)

            {

               return this.GetTable.Where<T>(exp);

            }

         

                (…)

    }


    When I call FindAll with an expression filter, the generated SQL doesn’t contain the WHERE clause. It’s a simple SELECT FROM.

    The SQL code is:


    Code Snippet

    The SQL code is:

     var subjects = from m in subjectsRepository.FindAll(

                                x=> x.id == util.id &&

                                x.anotherfield == util.anotherfield)

                   select m;


    What is really weird is... if I try the following query, the WHERE clause will be generated. Does anybody knows what is wrong with my original approach?


    Code Snippet

    SQLRepositoryDataContext c = new  SQLRepositoryDataContext ();

    var subjects = from x in c.GetTable<ClassX>().Where(a=>

                              a.id == util.id &&

                        a.anotherfield == util.anotherfield)

                   select x;





    Thursday, September 11, 2008 4:53 PM

Answers

  • The reason your query does not get the 'WHERE' clause added is because your code is calling the Enumerable.Where() extension method instead of the Queryable.Where() extension method.

     

    This happens because the Enumerable.Where() method takes a Func<T,bool> argument and the Queryable.Where() method takes an Expression<Func<T,bool>> argument.  The compiler is picking the Enumerable version since the Queryable version does not match.

     

    This should fix it:

     

    public IEnumerable<T> FindAll(Expression<Func<T, bool>> exp) where T : class

    {

                return dataContextFactory.Context.GetTable<T>().Where(exp);           

    }


     

    Thursday, September 11, 2008 10:54 PM
    Moderator

All replies

  • I can't see how your original code could figure out what table to run the query against.

     

    In the final working code snippet it is specified on GetTable.

     

    [)amien

    Thursday, September 11, 2008 5:38 PM
    Moderator
  • In my original code, when I use subjectsRepository, it have already been created before with the code:

    Code Snippet

     this.subjectsRepository= new SQLRepository<ClassX>(context);


    ClassX = Subject

    Did you understand? Do you know what can be wrong?



    Thursday, September 11, 2008 5:47 PM
  • Have you tried changing the line

     

               return this.GetTable.Where<T>(exp);

     

    to

     

               return this.GetTable<T>.Where(exp);

     

    [)amien

    Thursday, September 11, 2008 5:51 PM
    Moderator
  • Yes! I have already tried this before. I got the error... "The property SystemX.Data.DataAccess.SQLRepository<T>.GetTable cannot be used with type arguments.

    Sad
    Thursday, September 11, 2008 6:00 PM
  • Can you post the full exception to that error, I can't understand how it thinks GetTable is a property of SqlRepository<T> with that change.

     

    [)amien

     

    Thursday, September 11, 2008 6:21 PM
    Moderator
  • Because... it is!!! Sorry about this! Smile Look...

    Code Snippet

     private System.Data.Linq.Table<T> GetTable
      {
                get { return dataContextFactory.Context.GetTable<T>(); }
      }



    Thursday, September 11, 2008 6:26 PM
  • What happens if you do:

     

            public IEnumerable<T> FindAll<T>(Func<T, bool> exp)

            {

               return dataContextFactory.Context.GetTable<T>.Where(exp);

            }

     

    [)amien
    Thursday, September 11, 2008 6:47 PM
    Moderator
  • First of all, thank you very much  for your attention. If it's possible, keep sending suggestions Smile

    I did a little modification in your suggestion. So I have tried:

    Code Snippet

    public IEnumerable<T> FindAll(Func<T, bool> exp)

    {

                return dataContextFactory.Context.GetTable<T>().Where(exp);

               

    }


    The problem is… nothing happened again! No  where clause generated. I can’t imagine where the error can be. I am investigating… If I discover it, I’ll tell everyone.


    OBS.:

    Typing your original suggestion ...

    Code Snippet

    public IEnumerable<T> FindAll<T>(Func<T, bool> exp)

    {

                return dataContextFactory.Context.GetTable<T>().Where(exp);

               

    }

     

    I got the error message:

    Error      3             The type 'T' must be a reference type in order to use it as parameter 'TEntity' in the generic type or method 'System.Data.Linq.DataContext.GetTable<TEntity>()'                E:\Workspaces\SistemaMatricula\MAIN\SistemaMatricula.Data\DataAccess\SQLRepository.cs             27                20           SistemaMatricula.Data

     

    P.S.: I think you forgot the parenthesis after GetTable<> J

    Thursday, September 11, 2008 7:19 PM
  • One thing that i've noted is... in my original code (that doesn't work) the SQL generated is a simple SELECT FROM. BUT... in that code that works (bellow), the SQL code generated is a stored procedure (exec sp_executesql N...)

    Code Snippet

    SQLRepositoryDataContext c = new  SQLRepositoryDataContext ();

    var subjects = from x in c.GetTable<ClassX>().Where(a=>

                              a.id == util.id &&

                        a.anotherfield == util.anotherfield)

                   select x;

    Thursday, September 11, 2008 7:46 PM
  • You need to add the generic type constraint that tells the compiler that T is a reference type.

     

    public IEnumerable<T> FindAll<T>(Func<T, bool> exp) where T : class

    {

                return dataContextFactory.Context.GetTable<T>().Where(exp);           

    }

     

    Thursday, September 11, 2008 10:47 PM
    Moderator
  •  NepomucenoBR wrote:
    One thing that i've noted is... in my original code (that doesn't work) the SQL generated is a simple SELECT FROM. BUT... in that code that works (bellow), the SQL code generated is a stored procedure (exec sp_executesql N...)

    Code Snippet

    SQLRepositoryDataContext c = new  SQLRepositoryDataContext ();

    var subjects = from x in c.GetTable<ClassX>().Where(a=>

                              a.id == util.id &&

                        a.anotherfield == util.anotherfield)

                   select x;

     

     

    This is just the difference of having parameters or not.  This is the default behavior of the SqlCommand object.  If any parameters are specified it automatically switches to using 'sp_executesql'.

     

     

    Thursday, September 11, 2008 10:49 PM
    Moderator
  • The reason your query does not get the 'WHERE' clause added is because your code is calling the Enumerable.Where() extension method instead of the Queryable.Where() extension method.

     

    This happens because the Enumerable.Where() method takes a Func<T,bool> argument and the Queryable.Where() method takes an Expression<Func<T,bool>> argument.  The compiler is picking the Enumerable version since the Queryable version does not match.

     

    This should fix it:

     

    public IEnumerable<T> FindAll(Expression<Func<T, bool>> exp) where T : class

    {

                return dataContextFactory.Context.GetTable<T>().Where(exp);           

    }


     

    Thursday, September 11, 2008 10:54 PM
    Moderator
  • PERFECT!!! Thank you SO MUCH!!! Its working perfectly!!! My final code is:

    Code Snippet

    public IEnumerable<T> FindAll(Expression<Func<T, bool>> exp)
    {
           return this.GetTable.Where<T>(exp);
    }

    private System.Data.Linq.Table<T> GetTable
    {
          get { return dataContextFactory.Context.GetTable<T>(); }
    }



    I think I don't need to specify T as class in the method, once I did it in the class declaration. Am I wrong?

    Code Snippet

    public class SQLCatalogRepository<T> : IRepository<T>
            where T : class
    {

      (...)

    }


    Friday, September 12, 2008 4:45 PM
  • Great reply, Matt. So nice to see u here - your posts are invaluable - I was just runnin into the same situation.
    TYVM, Matt.
    Wednesday, September 24, 2008 5:31 AM