Joining Multiple Sequences

Thread is closed for posting
6 posts, 0 answers
    SMA SOFTWARE avatar
    11 posts
    Member since:
    May 2009

    Posted 04 Dec 2009 Link to this post


    I Got an error trying to join on multiple entities.

    I Have 3 tables/Classes
    EstoqueItens ( My Itens)
    EstoqueItensTipos ( My Iten's types)
    EstoqueItensStatus ( My Iten's Status)
    EstoqueQtdItem ( Where I record the actual quantity of my itens )

    I Just want to retrieve something like i Got with the SQL query:

            Item.Cod as Cod, 
            Item.Nome as Nome, 
            Item.Info as Info, 
            Item.QtdMin as QtdeMin, 
            Item.MesesValidade as Validade, 
            Item.Cat as Categoria, 
            Item.Status as StatusId, 
            S.Status as Status, 
            Item.Tipo as idTipo, 
            T.Tipo as Tipo, 
            Q.Qtd as Atual 
        EstoqueItens Item 
            inner join EstoqueItensTipo T on Item.Tipo = T.idTipo 
            inner join EstoqueItensStatus S on Item.Status = S.Id 
            inner join EstoqueQtdItem Q on Item.Cod = Q.Cod 

    I got success with LINQPad trying this
    var query =from Item in EstoqueItens 
                       join T in EstoqueItensTipos on Item.Tipo equals T.IdTipo 
                       join S in EstoqueItensStatuses on Item.Status equals S.Id 
                       join Qtde in EstoqueQtdItems on Item.Cod equals Qtde.Cod  
                       where Item.Empresa == 1 
                       orderby Item.Cat descending,Item.Cod descending 
                       select new { 
                           Cod = Item.Cod, 
                           Nome = Item.Nome, 
                           Info = Item.Info, 
                           QtdeMin = Item.QtdMin, 
                           Validade = Item.MesesValidade, 
                           Empresa = Item.Empresa, 
                           Categoria = Item.Cat, 
                           StatusId = Item.Status, 
                           Status = S.Status, 
                           TipoId = Item.Tipo, 
                           Tipo = T.Tipo, 
                           Atual = Qtde.Qtd                        
                       }  ; 
                       query.Dump ("Join query"); 

    But when I Tried the same with OpenAccessORM

    IObjectScope scope = ObjectScopeProvider1.ObjectScope(); 
    var data = from Item in scope.Extent<EstoqueItens>() 
                        join T in scope.Extent<EstoqueItensTipo>() on Item.Tipo equals T.IdTipo 
                        join S in scope.Extent<EstoqueItensStatus>() on Item.Status equals S.Id 
                        join Qtde in scope.Extent<EstoqueQtdItem>() on Item.Cod equals Qtde.Cod 
                        where Item.Empresa == 1 
                        orderby Item.Cat descending, Item.Cod descending 
                        select new 
                            Cod = Item.Cod, 
                            Nome = Item.Nome, 
                            Info = Item.Info, 
                            QtdeMin = Item.QtdMin, 
                            Validade = Item.MesesValidade, 
                            Empresa = Item.Empresa, 
                            Categoria = Item.Cat, 
                            StatusId = Item.Status, 
                            Status = S.Status, 
                            TipoId = Item.Tipo, 
                            Tipo = T.Tipo, 
                            Atual = Qtde.Qtd 
            RadGrid1.DataSource = data.ToList(); 

    I got this error
    Server Error in '/SICAI' Application. 
    Unable to cast object of type 'System.Object' to type 'OpenAccessRuntime.DataObjects.query.Node'. 
    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 
    Exception Details: System.InvalidCastException: Unable to cast object of type 'System.Object' to type 'OpenAccessRuntime.DataObjects.query.Node'. 
    Source Error: 
    Line 94:  
    Line 95:      
    Line 96:         RadGrid1.DataSource = data.ToList(); 
    Line 97:         data = null
    Line 98:  
    Source File: c:\Projetos\SMA.SICAI\Fontes\SMA.SICAI\SMA.SICAI.Web\Cliente\Protegidos\Estoque\_forms\CadastroItens.ascx.cs    Line: 96  

    I can put my database model here if needed


    SMA SOFTWARE avatar
    11 posts
    Member since:
    May 2009

    Posted 07 Dec 2009 Link to this post

    [Telerik.OpenAccess.Persistent(IdentityField = "cod")] 
        public partial class EstoqueItens 
            private int cod; // pk  
            private int cat; 
            private int empresa; 
            private string info; 
            private float? mesesValidade; 
            private string nome; 
            private int? qtdMin; 
            private int status; 
            private int tipo; 
            private EstoqueItensStatus estoqueItensStatus; 
            private EstoqueItensTipo estoqueItensTipo; 

    [Telerik.OpenAccess.Persistent(IdentityField = "id")] 
        public partial class EstoqueItensStatus 
            private int id; // pk  
            private string status; 

    [Telerik.OpenAccess.Persistent(IdentityField = "idTipo")] 
        public partial class EstoqueItensTipo 
            private int idTipo; // pk  
            private string tipo; 

    [Telerik.OpenAccess.Persistent(IdentityField = "cod")] 
        public partial class EstoqueQtdItem 
            private int cod; // pk  
            private int qtd; 
            private EstoqueItens estoqueIten; 

    SMA SOFTWARE avatar
    11 posts
    Member since:
    May 2009

    Posted 08 Dec 2009 Link to this post

  4. Jorge Arteiro
    Jorge Arteiro avatar
    5 posts
    Member since:
    Aug 2009

    Posted 09 Dec 2009 Link to this post

    Hi SMA,

    I am waiting the same thing for a long time. This should be the focus of the product in my opinion.
    The Telerik team is working hard on that issue at a moment, but it's still not working.

    I haven't tested OQL yet, but maybe it's could be a work around.

    It's very hard to create a Dynamic LINQ without these features.

    ps: Is it a Brazilian company? I am from Brazil as well, but working in Adelaide/Australia.



    Jorge Arteiro



  5. Alexander
    Alexander avatar
    727 posts

    Posted 09 Dec 2009 Link to this post

    Hello guys,

    We have made considerable improvements to our Linq provider last few weeks and this problem is resolved now. The query will work exactly as it is written in the first post with the next version of the product.

    @SMA SOFTWARE -  In the meantime you can use the following query which obtains the same result:
    var data = from Item in scope.Extent<EstoqueItens>()
           from T in scope.Extent<EstoqueItensTipo>()
           from S in scope.Extent<EstoqueItensStatus>()
           from Qtde in scope.Extent<EstoqueQtdItem>()
           where Item.Empresa == 1 &&
                 Item.Tipo == T.IdTipo &&
                 Item.Status == S.Id &&
                 Item.Cod == Qtde.Cod
           orderby Item.Cat descending, Item.Cod descending
           select new
               Cod = Item.Cod,
               Nome = Item.Nome,
               Info = Item.Info,
               QtdeMin = Item.QtdMin,
               Validade = Item.MesesValidade,
               Empresa = Item.Empresa,
               Categoria = Item.Cat,
               StatusId = Item.Status,
               Status = S.Status,
               TipoId = Item.Tipo,
               Tipo = T.Tipo,
               Atual = Qtde.Qtd
    It is tested with the latest internal build available on our site. If this does not work with your current version, please upgrade to the latest one (2009.3.1203.1).
    Thank you for the detailed description of the problem, hope that helps.

    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Watch a video on how to optimize your support resource searches and check out more tips on the blogs.
    SMA SOFTWARE avatar
    11 posts
    Member since:
    May 2009

    Posted 10 Dec 2009 Link to this post

    Hi @Jorge Thanks for the explanation,  we are a Brazilian company located at Granja Viana - Cotia - SP.

    @Alexander Your solution worked perfectly, thousand of thanks.

    would be a good idea to put this kind of syntax documented in ORM help page

    Telerik team always solve issues quickly here

    Thank you all

    Bruno Rocha
    SMA Software
Back to Top