locked
IList Extension not working with Entity Framework RRS feed

  • Question

  • I wrote a LINQ query that reads from an Entity's database and stores its results in a strongly typed class.  The thing is, the query uses a function I wrote that extends IList<> with a new method called TxtJoin.  Here it is: (This takes a list of values and merges them into a string with text between the elements .. like php's join function)

    namespace ArtGallery.Utility
    {
      public static class MyExtensions
      {
        public static string TxtJoin(this IList<string> thisArray, string thisChar)
        {
          string RetVal = "";
          foreach (string word in thisArray)
            RetVal += word + thisChar;
    
          return RetVal.Substring(0, RetVal.Length - thisChar.Length);
        }
      }
     }

    The extension works just fine, and the LINQ query works just fine as well:

          var i = from e in DB.Exhibits
              let collections =
                (from l in DB.ExhibitLinks
                 from c in DB.Collections
                 where l.collectionId == c.id
                 where l.exibitId == e.id
                 select c.name).ToList().TxtJoin(", ")
              select new ExhibitsHumanReadable
              {
                ExhibitId = e.id,
                Artist = String.Format("{0}, {1} {2}", e.Artist.last, e.Artist.first, e.Artist.middle),
                Type = e.Type.name,
                Medium = e.Medium.name,
                Title = e.title,
                Dimensions = e.dimensions,
                Description = e.description,
                Sold = e.sold,
                Visiable = e.visible,
                Published = e.publishDate,
                Collections = collections
              };
    

    And just for conpletness, heres the strongly typed class the results go into.

    namespace ArtGallery.Models
    {
      public class ExhibitsHumanReadable
      {
        public int ExhibitId { get; set; }
        public string Artist { get; set; }
        public string Type { get; set; }
        public string Medium { get; set; }
        public string Title { get; set; }
        public string Dimensions { get; set; }
        public string Description { get; set; }
        public bool Sold { get; set; }
        public bool Visiable { get; set; }
        public DateTime Published { get; set; }
        public string Collections { get; set; }
      }
    }
    
    The problem is I get the error message 

    LINQ to Entities does not recognize the method 'System.String TxtJoin(System.Collections.Generic.IList`1[System.String], System.String)' method, and this method cannot be translated into a store expression.

    I believe the problem is because, and please correct me if i'm wrong, LINQ dosn't actually render the values when you send it the query.  Instead it wwaits until they are accessed and at that time does not know about the function TxtJoin.  Is this correct?

    Anyway, I added a using to the entity's designer.cs file (something you shouldn't do)  and it was still unable to utalize the TxtJoin function.

    My question is, how do I tell Entities to LINQ about this custom function?



    Matthew Hazlett
    Clarity Computers
    http://www.devclarity.com/
    Wednesday, December 1, 2010 11:37 PM

Answers

  • Entity framework will attempt to translate your linq query into a TSQL query when you execute it. It has no way of knowing how to translate your TxtJoin method into SQL. Instead, break your query into two parts; one Linq-to-Entities query that retrieves the data from the database, then a Linq-to-objects query that calls the TxtJoin method.

    Also, take a look at string.Join - it does the same thing as your TxtJoin function.


     
       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Rule based class and property naming, Compare and Sync model <=> DB, Sync SSDL <=> CSDL (EF4)
     huagati.com/L2SProfiler - Query profiler for Linq-to-SQL and Entity Framework v4
    • Marked as answer by liurong luo Friday, December 10, 2010 2:20 AM
    Thursday, December 2, 2010 5:51 AM

All replies

  • I'm new t using LINQ, I think I am to a point where I can use it in a side project i'm building.  I wanted to get any thoughts on this query I wrote, there may be an easier way to do it.

    I have 7 tables, all the tables are related to eeachother.  Most of the tables enjoy a 1 to 1 relationship with another table (hence artistId = the ID attribute in the artists table, pretty basic stuff).  Now he only thing is a lookup table for "Collections" because one item can be in several collections.  For this I have the standard lookup table that matches collectionID's with ExhibitId's, again nothing earth shattering.

    The query I wrote to display all the information in a human readable format is as follows:

        public static class MyExtensions
    	{
    		public static string TxtJoin(this IList<string> thisArray, string thisChar)
    		{
    			string RetVal = "";
    			foreach (string word in thisArray)
    				RetVal += word + thisChar;
    
    			return RetVal.Substring(0, RetVal.Length - thisChar.Length);
    		}
    	}
    

    	var i = from e in Exhibits
    			let collections = 
    				(from l in ExhibitLinks
    				 from c in Collections
    				 where l.CollectionId == c.Id
    				 where l.ExibitId == e.Id
    				 select c.Name).ToList().TxtJoin(", ")
    			select new 
    			{
    				Artist = String.Format("{0}, {1} {2}", e.Artist.Last, e.Artist.First, e.Artist.Middle),
    				Type = e.Type.Name,
    				Medium = e.Medium.Name,
    				Title = e.Title,
    				Dimensions = e.Dimensions,
    				Description = e.Description,
    				Sold = e.Sold,
    				Visiable = e.Visible,
    				Published = e.PublishDate.ToShortDateString(),
    				Collections = collections
    			};
    

    Up where I do the let statement to get the collections, is there an easier way to do this?  Itt's basically a three table relationship

    Exhibits.ID == Exhibitlinks.ExhibitId == Exhibitlinks.CollectionId == Collections.Id == Collections.Name

     


    Matthew Hazlett
    Clarity Computers
    http://www.devclarity.com/
    • Merged by liurong luo Thursday, December 2, 2010 8:47 AM the same issue.
    Wednesday, December 1, 2010 2:33 PM
  • Entity framework will attempt to translate your linq query into a TSQL query when you execute it. It has no way of knowing how to translate your TxtJoin method into SQL. Instead, break your query into two parts; one Linq-to-Entities query that retrieves the data from the database, then a Linq-to-objects query that calls the TxtJoin method.

    Also, take a look at string.Join - it does the same thing as your TxtJoin function.


     
       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Rule based class and property naming, Compare and Sync model <=> DB, Sync SSDL <=> CSDL (EF4)
     huagati.com/L2SProfiler - Query profiler for Linq-to-SQL and Entity Framework v4
    • Marked as answer by liurong luo Friday, December 10, 2010 2:20 AM
    Thursday, December 2, 2010 5:51 AM
  • Hi hazlema,

    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!

    -Best Regards,
    Roahn Luo MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Welcome to Microsoft All-In-One Code Framework to download or request code samples from Microsoft Community Team!
    Wednesday, December 8, 2010 5:48 AM