[Solved] Joining Multiple Sequences

[SMA SOFTWARE]

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

[SMA SOFTWARE]

[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]

NOthing?

[Jorge Arteiro]

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

 

[Alexander]

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.

[SMA SOFTWARE]

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