how to convert left outer join sql query into entity framework 5.0?

Question

   using (database db = new database())
           { 
             var query = (
                          from c in db.tblBlogs

                         join a in db.tblBlogMedias on c.id equals  a.BlogId 


                         select new
                        {

                            c.id,
                            c.CreatedDate,
                            c.Author,
                            c.BlogTitle,
                            c.BlogDescription,
                            a.BlogId,
                            a.BlogPicturePath


                        }).OrderByDescending(d => d.id).ToList();


            System.Text.StringBuilder sb = new System.Text.StringBuilder();
           query.ToList().ForEach(x =>
                    {          


                            sb.Append(string.Format("<div  class='col-sm-4 
wow fadeInUp animated' data-wow-duration='1000ms' data-wow-delay='400ms' 
style='visibility: visible; animation-duration: 1000ms; animation-delay: 
400ms; animation-name: fadeInUp;'><div class='post-thumb'><div class='post-
meta'>"+
                                                     "</div>"+
                                                    "</div>"+
                                                    "<div class='entry-
header'><h3><a href='#'>{0}</a></h3><span class='date'>{1}</span></div>
</div>",x.BlogTitle,x.CreatedDate));

                    });
                 }

How do I write this sql query in var query = :

select tblBlog.*,tblBlogMedia.BlogPicturePath from tblBlog left outer join 
tblBlogMedia on tblBlog.id = tblBlogMedia.BlogId
where tblBlogMedia.id=(select max(id) from tblBlogMedia where BlogId='2')

Check this one out - https://stackoverflow.com/questions/3404975/left-outer-join-in-linq — Sergei G, Nov 01 '17 at 20:07

score 0 · Answer 1 · answered Nov 01 '17 at 20:23

0

With properly named entities and Navigation properties, it would look like this:

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EfDbFirstTest
{
    public class Blog
    {
        public int Id { get; set; }
        public DateTime CreatedDate { get; set; }
        public string Author { get; set; }
        public string BlogTitle { get; set; }
        public string BlogDescription { get; set; }
        public ICollection<BlogMedia> BlogMedia { get; set; }
    }

    public class BlogMedia
    {
        public int Id { get; set; }
        public int BlogId { get; set; }
        public Blog Blog { get; set; }
        public string BlogPicturePath { get; set; }
    }

    public class Db : DbContext
    {
        public DbSet<Blog> Blogs { get; set; }
        public DbSet<BlogMedia> BlogMedia { get; set; }
    }
    class Program
    {
        static void Main(string[] args)
        {


            using (var db = new Db())
            {
                db.Database.Log = m => Console.WriteLine(m);

                var q = from b in db.Blogs
                        select new
                        {

                            b.Id,
                            b.CreatedDate,
                            b.Author,
                            b.BlogTitle,
                            b.BlogDescription,
                            BlogPicturePath = b.BlogMedia.Any() ? b.BlogMedia.OrderByDescending(m => m.Id).FirstOrDefault().BlogPicturePath : null
                        };

                var results = q.ToList();



                Console.ReadKey();
            }
        }
    }
}

answered Nov 01 '17 at 20:23

David Browne - Microsoft

80,331
6
39
67

I am using asp.net c# , How could i bind these b.CreatedDate , b.Author etc in x.CreatedDate,x.Author ?? – inżynier umair Nov 01 '17 at 20:45
That should be a different question. – David Browne - Microsoft Nov 01 '17 at 20:52
i used your code according to my needs but the problem is in BlogPicturePath, It get the max value from tblBlogMedia and bind that value with all the records which is present in tblBlogs. I want each blog bind with its image only and that image is located at its maximum id in tblBlogMedia – inżynier umair Nov 01 '17 at 21:08
" It get the max value from tblBlogMedia and bind that value with all the records " No it doesn't. If you follow the Navigation Property from Blog to BlogMedia, each Blog gets a related BlogPicturePath. – David Browne - Microsoft Nov 01 '17 at 21:10
BlogPicturePath = db.tblBlogMedias.Any() ?db.tblBlogMedias.OrderByDescending(m => m.id).FirstOrDefault().BlogPicturePath : null; this is what i wrote. Im very close to my result,every thing works fine but the only image which is bind is located at last index value and this image binds with all the records..This is the problem, – inżynier umair Nov 01 '17 at 21:22
Exactly. You are not using a Navigation Property. You should not use "db.tblBlogMedias..." instead, it should be "b.tblBlogMedias...". – David Browne - Microsoft Nov 01 '17 at 21:25
but it shows an error that tblBlog does not contain a definion for blogs .. i used your code as it is. – inżynier umair Nov 01 '17 at 21:37
There's nothing called 'tblBlog' in the code I posted. – David Browne - Microsoft Nov 01 '17 at 21:40
yes , but i already have a table name is tblblog where as your table name is blog. i defined it also like: public class Db : DbContext { public DbSet Blogs { get; set; } public DbSet BlogMedia { get; set; } } – inżynier umair Nov 01 '17 at 21:46
Read: https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/navigation-property – David Browne - Microsoft Nov 01 '17 at 21:55
Thanks for the support sir ! still can't get the answer :( . No problem, i will find something else to resolve this. Thank You So Much Sir :) – inżynier umair Nov 01 '17 at 22:15

score 0 · Answer 2 · answered Nov 03 '17 at 22:38

This is how it works perfectly as i want, I write this code in ForEach loop body and then pass the value of q in sb.append :

                  var q = (from d in db.tblBlogMedias
                         join c in db.tblBlogs on d.BlogId equals x.id
                         select new
                         {
                             d.Id,
                             d.BlogPicturePath
                         }).OrderByDescending(d=>d.Id).Max(d => d.BlogPicturePath);

how to convert left outer join sql query into entity framework 5.0?

2 Answers2