none
C# LINQ to Entities RRS feed

  • Question

  • I'm new and have something wrong here - I think it has something to do with an anonymous type but not sure how to handle it.  What do I need to do so that I can call GetJobs from the UI?

    What am I doing wrong?

    public List<Job> GetJobs()
      {
        var context = new PrintWorksEntities();
        var query = from j in context.Jobs
              select new
              {
                j.JobId,
                j.JobDescription,
                j.SpecialInstructions,
                j.Customer.Name,
                j.DueDate
              };
        return query.ToList(); 
      }
    

    Sunday, June 19, 2011 3:56 AM

Answers

  • Hi!

    When using LINQ and select new {...} you create a anonymous entity, this cannot be automatically converted to a Job entity. You cannot either create a Job entity by using new Job, since this isn't allowed by the Entity Framework and you'll get a error.

    There are a few options for you.

    1. instead of calling select new { ... } you can simply call select j. This will return the Job entity with all it's field. I see that you also need to return Customer related info, so you should include the Customer navigation property by calling context.Jobs.Include("Customer"). Like this:

    public List<Job> GetJobs()
     {
      var context = new PrintWorksEntities();
      var query = from j in context.Jobs.Include("Customer")
         select j;
      return query.ToList(); 
     }
    
    

    2. if you only want to return the specified fields, you must create your own custom DTO object for the result, and do a select with this with select new JobDTO { ... } and return List<JobDTO> from your function.  Like this:

    public class JobDTO
    {
     public int JobId { get; set; }
     public string JobDescription { get; set; }
     public string SpecialInstructions { get; set; }
     public string CustomerName { get; set; }
     public DateTime DueDate { get; set; }
    }
    
    
    public List<JobDTO> GetJobs()
     {
      var context = new PrintWorksEntities();
      var query = from j in context.Jobs
         select new JobDTO
         {
          JobId = j.JobId,
          JobDescription = j.JobDescription,
          SpecialInstructions = j.SpecialInstructions,
          CustomerName = j.Customer.Name,
          DueDate = j.DueDate
         };
      return query.ToList(); 
     }
    
    

    3. the last option (but also the slowest), is for you to manually create a new Job for each of the result, by first fetching your the way you do, and then create a list of Job entities like this:

    public List<Job> GetJobs()
     {
      var context = new PrintWorksEntities();
      var result= from j in context.Jobs
         select new
         {
          j.JobId,
          j.JobDescription,
          j.SpecialInstructions,
          j.Customer.Name,
          j.DueDate
         }.ToList();
      return result.Select(row => new Job { JobId = row.JobId, JobDescription = row.JobDescription, SpecialInstructions = row.SpecialInstructions, Customer = new Customer() { Name = row.Name }, DueDate = row.DueDate }).ToList(); 
     }
    
    


    Every one of these has it's advantages/limitations. The advantage of the first one is that you can work with your complete entity, the drawback is the amount of data you're transferring.

    The advantage of the second one is that you limit the data to what is needed on the client side. The drawback is that you need to map data as the example shows when returning it to the caller, and if you want to pass a JobDTO back to the context, you need to convert the information back to the Job entity again.

    The third option is a bit slower than the two first, since it first creates an anonymous list, and then create a new list of real Job entities. I would not recommend this option.

    Dependent of what the caller needs, I would go for either option 1 or 2.

    Hope this helps!


    --Rune
    • Marked as answer by Brian Kidd Sunday, June 19, 2011 8:55 PM
    Sunday, June 19, 2011 7:03 AM

All replies

  • Hi!

    When using LINQ and select new {...} you create a anonymous entity, this cannot be automatically converted to a Job entity. You cannot either create a Job entity by using new Job, since this isn't allowed by the Entity Framework and you'll get a error.

    There are a few options for you.

    1. instead of calling select new { ... } you can simply call select j. This will return the Job entity with all it's field. I see that you also need to return Customer related info, so you should include the Customer navigation property by calling context.Jobs.Include("Customer"). Like this:

    public List<Job> GetJobs()
     {
      var context = new PrintWorksEntities();
      var query = from j in context.Jobs.Include("Customer")
         select j;
      return query.ToList(); 
     }
    
    

    2. if you only want to return the specified fields, you must create your own custom DTO object for the result, and do a select with this with select new JobDTO { ... } and return List<JobDTO> from your function.  Like this:

    public class JobDTO
    {
     public int JobId { get; set; }
     public string JobDescription { get; set; }
     public string SpecialInstructions { get; set; }
     public string CustomerName { get; set; }
     public DateTime DueDate { get; set; }
    }
    
    
    public List<JobDTO> GetJobs()
     {
      var context = new PrintWorksEntities();
      var query = from j in context.Jobs
         select new JobDTO
         {
          JobId = j.JobId,
          JobDescription = j.JobDescription,
          SpecialInstructions = j.SpecialInstructions,
          CustomerName = j.Customer.Name,
          DueDate = j.DueDate
         };
      return query.ToList(); 
     }
    
    

    3. the last option (but also the slowest), is for you to manually create a new Job for each of the result, by first fetching your the way you do, and then create a list of Job entities like this:

    public List<Job> GetJobs()
     {
      var context = new PrintWorksEntities();
      var result= from j in context.Jobs
         select new
         {
          j.JobId,
          j.JobDescription,
          j.SpecialInstructions,
          j.Customer.Name,
          j.DueDate
         }.ToList();
      return result.Select(row => new Job { JobId = row.JobId, JobDescription = row.JobDescription, SpecialInstructions = row.SpecialInstructions, Customer = new Customer() { Name = row.Name }, DueDate = row.DueDate }).ToList(); 
     }
    
    


    Every one of these has it's advantages/limitations. The advantage of the first one is that you can work with your complete entity, the drawback is the amount of data you're transferring.

    The advantage of the second one is that you limit the data to what is needed on the client side. The drawback is that you need to map data as the example shows when returning it to the caller, and if you want to pass a JobDTO back to the context, you need to convert the information back to the Job entity again.

    The third option is a bit slower than the two first, since it first creates an anonymous list, and then create a new list of real Job entities. I would not recommend this option.

    Dependent of what the caller needs, I would go for either option 1 or 2.

    Hope this helps!


    --Rune
    • Marked as answer by Brian Kidd Sunday, June 19, 2011 8:55 PM
    Sunday, June 19, 2011 7:03 AM
  • Thank you, Rune for your answer!  I really appreciate it.

    bkidd

    Sunday, June 19, 2011 8:55 PM