Joining Multiple Sequences

6 posts, 0 answers
  1. SMA SOFTWARE
    SMA SOFTWARE avatar
    11 posts
    Member since:
    May 2009

    Posted 04 Dec 2009 Link to this post

    HI,

    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:

    select  
            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 
    from 
        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

    Thanks


  2. SMA SOFTWARE
    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; 
     
     
     
     
     
        } 

  3. DevCraft banner
  4. SMA SOFTWARE
    SMA SOFTWARE avatar
    11 posts
    Member since:
    May 2009

    Posted 08 Dec 2009 Link to this post

    NOthing?
  5. 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.

     

    Cheers,

    Jorge Arteiro

    WorldWeb

     

  6. Alexander
    Admin
    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.

    Greetings,
    Alexander
    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.
  7. SMA SOFTWARE
    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
    @rochacbruno
    SMA Software
Back to Top
DevCraft banner