locked
Shaping EF LINQ Query Results With Many-to-Many RRS feed

  • Question

  • In my database I have an Event table, a Doc table, and an EventDoc table that supports a many-to-many relationship between Event and Doc.  My problem is that I'd like to create an EF/LINQ query that joins across the three tables and control which columns show up in the resulting projection.

    In a SQL terms, here's what I'm looking for:

     

    SELECT 
    	e.EventDate
    	,d.Filename
    FROM 
    	Event e
    	inner join EventDoc ed on ed.EventID = e.EventID
    	inner join Doc d on d.DocID = ed.DocID
    

    All I've been able to generate in LINQ so far is something equivalent to this:

     

    SELECT 
    	e.*
    	,d.*
    FROM 
    	Event e
    	inner join EventDoc ed on ed.EventID = e.EventID
    	inner join Doc d on d.DocID = ed.DocID
    

     

    So I've generated an EF model from my database and all of my tables have proper FK's.  Here's my C# code to generate the query:

     

    var query = context.Event
        .Include("EventDoc.Doc")
        .Select(x => new
        {
            EventDate = x.EventDate,
            FileName = x.EventDoc.Doc.Filename
        });

     

    The compiler complains with the following two error messages:

     

    Error 20 Cannot convert lambda expression to type 'string' because it is not a delegate type

     

    Error 21 'System.Data.Objects.DataClasses.EntityCollection<DataModel.EventDoc>' does not contain a definition for 'Doc' and no extension method 'Doc' accepting a first argument of type 'System.Data.Objects.DataClasses.EntityCollection<DataModel.EventDoc>' could be found (are you missing a using directive or an assembly reference?)

    Now I know my model my EventDoc entity does indeed have a definition for Doc so I'm not sure how I'm supposed to do this.

    Any ideas?
    Thx, Mark

     

    Monday, January 17, 2011 6:43 AM

Answers

  •  

    Hi sisdog,

    Thanks for your post.

    According to your description, There are three tables in your application, The EventDoc table has two foreign keys(EventID, DocID), am I right?

    If so, Let's look at your query sentence:

    var query = context.Event  .Include("EventDoc.Doc")  .Select(x => new  {    EventDate = x.EventDate,    FileName = x.EventDoc.Doc.Filename  });
    // x.EventDoc is a collection has the same EventID, so this syntax is error.
    
    
    

    I don't have the three table, but I create three table have the same relationship. Here is my query, and it works on my compurter:

    var customer = context.Customers.Include("Orders.Items")
              .Select(c => new { CustomerName=c.CustomerName,ItemName=c.Orders.Select(o=>o.Item.ItemName)})
              .FirstOrDefault();
            foreach (var orders in customer.ItemName)
            {
              Console.WriteLine(orders);
            }
    
    
    

    Table Customers(Event)/ Orders(EventDoc)/Items(Doc).

    If I misunderstand you, please feel free to follow up.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Alan_chen Friday, January 28, 2011 1:27 AM
    Tuesday, January 18, 2011 6:01 AM

All replies

  •  

    Hi sisdog,

    Thanks for your post.

    According to your description, There are three tables in your application, The EventDoc table has two foreign keys(EventID, DocID), am I right?

    If so, Let's look at your query sentence:

    var query = context.Event  .Include("EventDoc.Doc")  .Select(x => new  {    EventDate = x.EventDate,    FileName = x.EventDoc.Doc.Filename  });
    // x.EventDoc is a collection has the same EventID, so this syntax is error.
    
    
    

    I don't have the three table, but I create three table have the same relationship. Here is my query, and it works on my compurter:

    var customer = context.Customers.Include("Orders.Items")
              .Select(c => new { CustomerName=c.CustomerName,ItemName=c.Orders.Select(o=>o.Item.ItemName)})
              .FirstOrDefault();
            foreach (var orders in customer.ItemName)
            {
              Console.WriteLine(orders);
            }
    
    
    

    Table Customers(Event)/ Orders(EventDoc)/Items(Doc).

    If I misunderstand you, please feel free to follow up.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Alan_chen Friday, January 28, 2011 1:27 AM
    Tuesday, January 18, 2011 6:01 AM
  • Hi sisdog,

     I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions? 
     
    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, January 21, 2011 1:24 AM
  • Hi,

    As a related reading, you might also want to read up this blog post for some examples: Entity Framework: Queries involving many to many relationship tables

    Regards,


    Syed Mehroz Alam
    My Blog | My Articles
    Friday, January 21, 2011 7:20 AM
  • Try:

     

    var query = context.Event
    	.Select( x => new
    	{
    		EventDate = x.EventDate,
    		FileNames = x.Docs.Select( d => d.FileName )
    	} );
    
    
    


    Which when executed provides an anonymous type with two properties, an EventDate DateTime and an IEnumerable<string> with your filenames.

    I don't believe you need the .Include() since you are projecting away your Event entities anyway.  If it turns out you do (I don't remember 100%), it would be .Include("Docs"). 

    Friday, January 21, 2011 11:24 PM