CODEWITHSUNDEEP
c#linqentity-framework-6
Ninjanoel
Ninjanoel

Reputation: 2914

Entity Framework returning IQueryable of join of two tables

I'd like to use Entity Framework to return data from 2 tables, and a selection of columns of the 2 tables, at first I was not having much luck with something simple, like returning int's and strings (ie. select new { id = t1.ID, other = t2.OtherData }, as casting that anonymous type was cumbersome at the destination (destination being my winform), so I struck on the idea to just return both table rows...

So something like this :

  public static IQueryable<{Table1,Table2}> byRecID(Guid recID, MyContext DBContext)
  {
      return (from i1 in DBContext.Table1
             join j1 in DBContext.Table2 on i1.GroupID equals j1.GroupID
             where i1.RecID.Equals(RecID)
             select new { i1, j1 }).SingleOrDefault();
  }

This is all fine EXCEPT the return type of the method is incorrect. I've tried a few combinations. Unfortunately, when I call "byRecID" from the winform, "SingleOrDefault" is not available, but IS available inside 'byRecID' method, so I can't just return IQueryable, needs to be the typed IQueryable<SOMETHING IN HERE> (because SingleOrDefault not an extension of IQueryable, only IQueryable<T>).

My Question... is there a syntax for 'SOMETHING IN HERE' that lets me specify its a join of two table rows?

and I'm wondering... Why is SingleOrDefault an option INSIDE the method but not an option of the result of the method when called from my winform?

Basically I was hoping for something that allows clean data calls from my winforms, without casting to hideous anonymous types then using reflection (like when I returned anonymous type of primitives), but also don't want to spawn a Type just for use by my byRecID method.

Upvotes: 4

Views: 11635

Answers (3)

vasil oreshenski
vasil oreshenski

Reputation: 2836

In C# anonymous types are best used in the scope of the same method where you project them. As you have seen for your self they can't be used outside of the defining method. So you can't return anonymous types from methods (and keep their structure, you can always return them as objects, but this is not good idea) this makes your syntax of the method invalid.

Best option is to project the result to class specified for your needs.

public class TableJoinResult
{
   public Table1 Table1 { get; set; }
   public Table2 Table2 { get; set; }
}

Your query:

 public static IQueryable<TableJoinResult> byRecID(Guid recID, MyContext DBContext)
  {
      return (from i1 in DBContext.Table1
             join j1 in DBContext.Table2 on i1.GroupID equals j1.GroupID
             where i1.RecID.Equals(RecID)
             select new TableJoinResult { Table1= i1, Table2 = j1 }).SingleOrDefault();
  }

More info on anonymous types: https://msdn.microsoft.com/en-us/library/bb397696.aspx

Upvotes: 8

anu
anu

Reputation: 315

I wanted to avoid creating a specific class or a struct. The below is a lengthy way that avoids creating a struct/class. So I did this in my service class:-

 List<KeyValuePair<string,int>> IWorkflowService.GetServiceRetention()
        {
           var serviceRetention =  from y in _context.SERVICES
                     join z in _context.SERVICE_SETTINGS on y.ID equals z.SERVICEID
                     select new { Service= y.SERVICE, Retention=z.RETENTION };//new KeyValuePair<string,int?>( y.SERVICE, z.RETENTION ));
            var list = new List<KeyValuePair<string, int>>();
            foreach (var obj in serviceRetention)
            {
                list.Add(new KeyValuePair<string, int>(obj.Service,Convert.ToInt32(obj.Retention)));
            }
            return list;
        }

I had to do this in my controller class:

List<KeyValuePair<string, int>> c = _service.GetServiceRetention();

            foreach (var obj in c)
            {
                 //The key contains service and value contains Rtention.
                  RecurringJob.AddOrUpdate(DELETE_SERVICE + obj.Key, () =>
                  Delete(obj.Key), //this is my function that does the actual process
               Cron.DayInterval(Convert.ToInt32(obj.Value)));
            }

I am not sure if this is the best way to do it, but works for me.

Upvotes: 0

George Vovos
George Vovos

Reputation: 7618

1)Create a specific class that describes you result.

2)See 1

Upvotes: 2

Related Questions