CODEWITHSUNDEEP
c#.netlinqgenericslinq-to-sql
Sridhar
Sridhar

Reputation: 503

LINQ join using Generics

I'm trying to write LINQ join query In generics, but having trouble in identifying the Foreign Key. PFB code.

I want to identify the Foreign Key present in table U, which I can use it for the comparison operation.Thanks

var _tab = (from tblT in context.GetTable<T>()
            join tblU in context.GetTable<U>()
            on pk equals fk 
            select tblT).GetEnumerator();

Upvotes: 2

Views: 2513

Answers (2)

Udara Kasun
Udara Kasun

Reputation: 2216

I have improved @Gary.S answer. here I improved regarding query selector and how to use where close in here. I think this will help full for you.

this is the generic method which I updated.

Note : AppDbContext is my dbContext

public class MyGenMetClass
{
    public async Task<IEnumerable<TResult>> FetchJonBy<TOuter, TInner, TKey, TResult>(Expression<Func<TOuter, TKey>> outerKeySelector, Expression<Func<TInner, TKey>> innerKeySelector, Expression<Func<TOuter, bool>> wherOuter, Expression<Func<TInner, bool>> wherInner, Expression<Func<TOuter, TInner, TResult>> resultSelector) where TOuter : class where TInner : class where TResult : class
     {
       try
       {
           using (AppDbContext db = new AppDbContext())
           {
                var data = await db.Set<TOuter>().Where(wherOuter).Join(db.Set<TInner>().Where(wherInner), outerKeySelector, innerKeySelector, resultSelector).ToListAsync();
                    return data;
            }
       }catch (Exception ex){
            return null;
       }
   }
}

and my model like this.

SupplierModel Class

public class SupplierModel
{
        [Key]
        public int SupplierId { get; set; }
        public string Name { get; set; }
        public int CategoryId { get; set; }
}

SupplierCatgory Class

public class SupplierCatgory
{
       [Key]
       public int CategoryId { get; set; }
       public string Category { get; set; }        
       public Boolean IsActive { get; set; } = true;  
}

SupplierViewModel Class

public class SupplierViewModel
{       
        public int SupplierId { get; set; }
        public string Name { get; set; }
        public int CategoryId { get; set; }
        public string Category { get; set; }
}

here I joined SupplierModel and SupplierCatgory and return SupplierViewModel

And you can call like this

var result =await new MyGenMetClass().FetchJonBy<SupplierModel,SupplierCatgory,int, SupplierViewModel>(s=> s.CategoryId,c=>c.CategoryId ,m=>m.IsAcvtive==true,c=>c.IsActive==true, (s, y) => new SupplierViewModel() { Name=s.Name,Category=y.Category} );

Upvotes: 0

Gary.S
Gary.S

Reputation: 7131

You mention that you want to "identify the Foreign Key present in table U". While you may be able to do that via reflection and some sort of convention it seems like it would be a brittle solution. I would suggest that you provide the primary/foreign key relationship via a delegate.

Basically a join uses a delegate for retrieving the primary and foreign keys and the LINQ provider translates that into the join clause. The signature of each side of the join is basically the same, in your example it would be: Expression<Func<T, TKey>> and Expression<Func<U, TKey>>. It is important to note the the key type must be the same for both sides of the join.

Whatever you use to call this method should require that these delegates be passed in. It might look something like this:

public class Query
{
    public IEnumerable<T> GetData<T, U, TKey>(Expression<Func<T, TKey>> tKey, Expression<Func<U, TKey>> uKey)
    {
        Context context = new Context();
        // using the extension method as the query expression had trouble figuring out the types
        var data = context.GetTable<T>().Join(context.GetTable<U>(), tKey, uKey, (tblT, tblU) => tblT);            

        return data;
    }
}

Calling it would look like this:

var data = query.GetData<Person, Order, int>(person => person.Id, order => order.Orderer.Id);

Upvotes: 6

Related Questions