Posts
12
Comments
10
Trackbacks
0
February 2011 Entries
[LINQ] Master – Detail Same Record(II)

In my previous post, I introduced my problem, but I didn’t explain the problem with Entity Framework

When you try the solution indicated you will take the following error:

LINQ to Entities don’t recognize the method 'System.String Join(System.String, System.Collections.Generic.IEnumerable`1[System.String])’ of the method, and this method can’t be translated into a stored expression.

The query that produces that error was:

   1: var consulta = (from TCabecera cab in 
   2:                  contexto_local.TCabecera  
   3:    let Detalle = (from TDetalle detalle 
   4:                    in cab.TDetalle 
   5:    select detalle.Nombre)    
   6:    let Nombres = string.Join(",",Detalle )     
   7:    select new   
   8:       {  
   9:        cab.Campo1, 
  10:        cab.Campo2, 
  11:        Nombres
  12:        }).ToList();
  13: grid.DataSource=consulta;

 

Why is this error happening?

This error happens when the query couldn’t be translated into T-SQL.

Solutions?

To quit that error, we need to execute the query on 2 steps:

   1: var consulta = (from TCabecera cab in   
   2:                 contexto_local.TCabecera    
   3:       let Detalle = (from TDetalle detalle   
   4:                      in cab.TDetalle   
   5:                      select detalle.Nombre)      
   6:      select new     
   7:       {    
   8:         cab.Campo1, 
   9:         cab.Campo2,  
  10:         Detalle 
  11:         }).ToList();  
  12: var consulta2 = (from dato in consulta
  13:             let Nombes = string.Join(",",dato.Detalle)
  14:             select new 
  15:             {
  16:             dato.Campo1,
  17:             dato.Campo2,
  18:             Nombres
  19:             };
  20:         grid.DataSource=consulta2.ToList();

Curiously

This problem happens with Entity Framework but, the same problem can’t be reproduced on LINQ – To – SQL, that it works fine in one unique step.

Hope It’s helpful

Best Regards

Posted On Thursday, February 17, 2011 1:04 PM | Comments (0)
[LINQ] Master–Detail Same Record (I)

PROBLEM

Firstly, I am working on a project based on LINQ, EF, and C# with VS2010.

The following Table shows what I have and what I want to show.

Header
C1 C2 C3
1 P1 01/01/2011
2 P2 01/02/2011
Details
1 1 D1
2 1 D2
3 1 D3
4 2 D1
5 2 D4
Expected Results
1 P1 01/01/2011 D1, D2, D3
2 P2 01/02/2011 D1,D4

 

IDEAS

At the begin I got 3 possible ways:

- Doing inside the DB:  It could be achieved from DB with a CURSOR in a Stored Procedure.

- Doing from .NET with LOOPS.

- Doing with LINQ (I love it!!)

FIRST APROX

Example with a simple CLASS with a LIST:

With and Employee Class that acts as Header Table:

   1: public class Employee 
   2: {
   3:     public Employee () { }
   4:     public Int32 ID { get; set; }
   5:     public String FirstName{ get; set; }
   6:     public String LastName{ get; set; }
   7:     public List<string> Numbers{ get; set; } // Acts as Details Table
   8: }
We can show all numbers contained by Employee:
   1: List<Employee > listado = new List<Employee >();
   2: //Fill Listado
   3: var query= from Employee em in listado
   4:     let Nums= string.Join(";", em.Numbers)
   5:     select new { 
   6:     em.Name,
   7:     Nums
   8:     };

The “LET” operator allows us to host the results of “Join” of the Numbers List of the Employee Class.

A little example.

ASAP I will post the second part to achieve the same with Entity Framework.

Best Regards

Posted On Wednesday, February 16, 2011 10:36 PM | Comments (0)
Starting in GeeksWithBlog

It’s a great pleasure for me to start writing in a big Community like GeeksWithBlog.

Firstly, I want to present myself: My name is Javier Torrecilla and I am a .NET 4x4 Developer. Currently I write also in Spanish in my Spanish Blog.

Well, What kind of topics are you going to read here?

All kind related to .NET Technologies: C#, VB, ASP.NET, MVC, EF, …, Databases: Oracle, SQL Server…

Hope my post will be useful for all.

Best Regards!

Posted On Wednesday, February 16, 2011 3:54 PM | Comments (0)