CODEWITHSUNDEEP
c#mysqlsql-serverentity-framework
Sivamohan Reddy
Sivamohan Reddy

Reputation: 574

linq: left join in linq with different type of conditions

I've two tables like promotionalOffers and PromotionalOffersUsed
I'm getting the matching records using joins now i wanted to include different conditions like PromotionalOffers.ISVCSPId =10 and PromotionalOffersUsed.OfferId is null

I've written query in sql using left join working expected but I'm unable to write same query in linq

here my sql query

SELECT * 
FROM PromotionalOffers
left JOIN PromotionalOffersUsed ON PromotionalOffers.Id = PromotionalOffersUsed.OfferId where PromotionalOffers.ISVCSPId =10 and PromotionalOffersUsed.OfferId is null

my linq query is

(from s in db.PromotionalOffers
 join e in db.PromotionalOffersUsed on s.Id equals e.OfferId
 where s.ISVCSPId == iSVCSPData.Id
 select s).ToListAsync();

I'm unable to include left join here

Upvotes: 0

Views: 1501

Answers (3)

Harald Coppoolse
Harald Coppoolse

Reputation: 30454

Apparently you have a one-to-many relation between PromotionalOffers and PromotionalOffersUsed: Every PromotionalOffer has zero or more PromotionalOffersUsed, and every PromotionalOffersUsed belongs to exactly one PromotionalOffer.

If you follow the entity framework conventions to design this one-to-many relation, you would not need to do a join, nor a left-outerjoin, you would use the ICollection classes instead:

class PromotionalOffer
{
    public int Id {set; set;}
    // a PromotionalOffer has zero or more PromotionalOfferUsed
    public virtual ICollection<PromotionalOfferUsed> PromitionalOffersUsed {get; set;}
    ...
}
class PromotionalOfferUsed
{
    public int Id {set; set;}
    // every PromotionalOffersUsed belongs to exactly one PromotionalOffer
    // using foreign key:
    public int PromotionalOfferId {get; set;}
    public PromotionalOffer PromotionalOffer {get; set;}
    ...
}
class MyDbContext: DbContext
{
    public DbSet<PromotionalOffer> PromotionalOffers {get; set;}
    public DbSet<PromotionalOfferUsed> PromotionalOffersUsed {get; set;
}

Normally this would have been enough for entity framework to know that you designed a one-to-many relation.

The only reason we need some fluent API is because of the strange plurals:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    // PromotionalOffersUsed are stored in a table with name PromotionOffersUsed:
    modelBuilder.Entity<PromotionalOfferUsed>().ToTable("PromotionalOffersUdsed");

    // Every PromotionalOffer has zero or more PromotionalOfferUsed
    // every PromotionalOfferUsed belongs to exactly one (required) PromotionalOffer
    // using foreign key PromtionalOfferId
    modelBuilder.Entity<PromotionalOffer>
        .HasMany(promotionalOffer => promotionalOffer.PromotionalOffersUsed)
        .WithRequired(promotionalOfferUsed => promotionOfferUsed.PromotionalOffer)
         .HasForeignKey(post => post.BlogId);

    base.OnModelCreating(modelBuilder);
}

Once again, if you had standard singulars and plurals (Person/Persons; Account/Accounts, Order/Orders), this fluent API was not needed.

Now your query: think in Collections, not in Joins.

Give me all PromotionalOffers that have ISVCSPId equal to 10, together with all their PromotionalOffersUsed that have OfferId equal to null

using (var dbContext = new MyDbContext())
{
    var result = dbContext.PromotionalOffers
        .Where(promotionalOffer => promotionalOffer.ISVCSPID == 10)
        .Select(promotionalOffer => new
        {   // select only the properties you will be using:
            ISVCSPID = promotionalOffers.ISVSPID,
            ... // other properties from promotionalOffers

            PromotionalOffersUsed = promotionalOffers.PromtionalOffersUsed
                .Where(promotionalOffersUsed => promotionalOffersUsed.OfferId == null)
                .Select(promotionalOffersUsed => new
                {
                     // again: select only the properties of PromotionalOffersUsed
                     // that you will be using
                     Id = promotionalOffersUsed.Id,
                     ... // other properties
                })
                .ToList();
        }
}

Because entity framework knows that there is a one-to-many with a foreign key, it will do the proper left outer join for you.

Upvotes: 0

Emre Kabaoglu
Emre Kabaoglu

Reputation: 13146

Try like this;

(from s in db.PromotionalOffers
 join e in db.PromotionalOffersUsed on s.Id equals e.OfferId into joinT
 from e in joinT.DefaultIfEmpty()
 where s.ISVCSPId == 10 && (e == null || e.OfferId == null)
 select new { PromotionalOffers = s, PromotionalOffersUsed = joinT } ).ToListAsync();

Upvotes: 1

Pranay Rana
Pranay Rana

Reputation: 176896

for left join you have to do like this , following is example of left outer join in linq 

var leftFinal =
        from l in lefts
        join r in rights on l equals r.Left into lrs
        from lr in lrs.DefaultIfEmpty()
        select new { LeftId = l.Id, RightId = ((l.Key==r.Key) ? r.Id : 0 };

Query 

SELECT * 
FROM PromotionalOffers
left JOIN PromotionalOffersUsed ON PromotionalOffers.Id = PromotionalOffersUsed.OfferId where PromotionalOffers.ISVCSPId =10 and PromotionalOffersUsed.OfferId is null

Tentative query in linq

var leftFinal =
        (from l in PromotionalOffers.Where(p=> p.ISVCSPId ==10) 
        join r in PromotionalOffersUsed on l.ID equals r.OfferId  into lrs
        from lr in lrs.DefaultIfEmpty()
        select 
          new { LeftId = l.Id, RightId = ((l.ID==r.OfferId  ) ? r.OfferId   : -1 }
        ).where(d=> d.RightID != -1);

Upvotes: 1

Related Questions