How get this Entity Framework SQL Query to run properly

Question

On my setup have I have the following query.

Class:

public class NewsList
{
    public Guid NewsGuid { get; set; }
    public string Heading { get; set; }
    public string FileName { get; set; }
}

Controller:

List<NewsList> newsList = (from n in db.News
            join ni in db.NewsImages
            on n.NewsGuid equals ni.NewsGuid
            where (ni.FileOrder == 10 || ni.FileName == null) && n.NewsDate.Year == newsDate.Year && n.NewsDate.Month == newsDate.Month && n.NewsDate.Day == newsDate.Day
            orderby n.NewsDate descending
            select new NewsList
            {
                NewsGuid = n.NewsGuid,
                Heading = n.Heading,
                FileName = ni.FileName
            }).Take(10).ToList<NewsList>();

I have two issues with this.

1) I am joining two tables here, which are News and NewsImages and its a 1-N relation. One news can have nothing, one or more images. With this joining, I don't get news without image even though I specified ni.FileName can be null. It only returns News, which has at least a record in the NewsImages table. I just want a regular type of LEFT JOIN here. How do I achieve that?

2) the line with the WHERE statement it looks terrible that I need to check the year, month and day separately. NewsDate is a DateTime field on the database and newsDate is date only format (yyyy-MM-dd). I just want to select all the news from a specific date(querystring). Without EF I can achieve this with standard T-SQL like this:

CAST(NewsDate AS date)='" + newsDate + "'"

How do I clean that WHERE line better?

Does n.NewsDate.Date == newsDate.Date suit your need in the where clause? — JD Davis
– JD Davis, Commented Feb 12, 2020 at 0:20
@JDDavis I have tried that. It did throw the error "The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported." — Danny Web
– Danny Web, Commented Feb 12, 2020 at 0:25
its normal ASP.NET MVC project with EF 6. Runtime version: v4.0.30319 & Version 6.0.0.0 — Danny Web
– Danny Web, Commented Feb 12, 2020 at 0:28

JD Davis · Accepted Answer · 2020-02-12 03:01:21Z

3

Assuming this is EF Core, you should be able to simply compare the dates directly using n.NewsDate.Date == newsDate.Date. If this is a previous version, you'll need to import DbFunctions, and you'll be able to use DbFunctions.TruncateTime(n.NewsDate) == DbFunctions.TruncateTime(newsDate)

To perform a left outer join, you'll need to tell EF to return the empty list using DefaultIfEmpty().

List<NewsList> newsList = 
            (from n in db.News.Where(mn => DbFunctions.TruncateTime(mn.NewsDate) == DbFunctions.TruncateTime(newsDate))
                    join ni in db.NewsImages.Where(mni => mni.FileOrder == 10 || mni.FileName == null)
                        on n.NewsGuid equals ni.NewsGuid into nni
                    from sublist in nni.DefaultIfEmpty()
                    orderby n.NewsDate descending
                    select new NewsList
                    {
                        NewsGuid = n.NewsGuid,
                        Heading = n.Heading,
                        FileName = sublist.FileName
                    }).Take(10).ToList<NewsList>()

edited Feb 12, 2020 at 3:01

answered Feb 12, 2020 at 0:29

JD Davis

3,7705 gold badges32 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Danny Web Over a year ago

from sublist in nni.DefaultIfEmpty() throws an error

Danny Web Over a year ago

now the nni.DefaultIfEmpty() ok.. but the next where line throws error.. it says the name ni does not exists in the current context. it throws that error for both ni.FileOrder and ni.FileName

JD Davis Over a year ago

@user2160310 How about now?

Danny Web Over a year ago

the DbFunctions.TruncateTime is gone and it doesnt return any records on the selected date. I have added the line: from n in db.News.Where(nd => DbFunctions.TruncateTime(nd.NewsDate) == DbFunctions.TruncateTime(newsDate) and its working now. thx man!

Steve Py · Accepted Answer · 2020-02-12 01:07:48Z

1

Firstly, navigation properties would help here. EF can map the relationships and compose SQL rather than trying to transpose LINQ into SQL.

If the relationship between News and NewsImage is mapped where News contains:

Inside the News class:

public virtual ICollection<NewsImage> NewsImages { get; set; } = new List<NewsImage>();

Inside the NewsImage class:

public virtual News News { get; set; }

If your NewsImage also contains a NewsGuid property, then this can be related to the News navigation property:

[ForeignKey("News")]
public Guid NewsGuid { get; set; }

Finally the mapping. This can be done using an EntityTypeMapping definition that EF can load to understand the relationship between Entities, or via the OnModelCreating event and it's modelBuilder in the DbContext. With modelBuilder it would look something like:

public override OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<News>().HasMany(x => x.NewsImages).WithRequired(x => x.News);
}

The controller query would look more like:

newsDate = newsDate.Date;
var cutOff = newsDate.AddDays(1);
var news = db.News
    .Where(n => n.NewsDate >= newsDate && n.NewsDate < cutOff);

This will get you the news for the given date. The second part appears that you want to filter out only the images for any articles (if any) that meet a criteria, but still display a news list item if there aren't any images.

var newsListItems = db.News
    .Where(n => n.NewsDate >= newsDate && n.NewsDate < cutOff)
    .SelectMany(n => n.NewsImages
        .Where(ni => ni.FileOrder == 10)
        .Select(ni => new NewsList
        {
            NewsGuid = n.NewsGuid,
            Heading = n.Heading,
            FileName = ni.FileName
        })).OrderByDescending(n => n.NewsDate)
       .Take(10).ToList();

This gets you to where you currently are, but would only include News that had Images.

To include the News without images:

var newsListItems = db.News
    .Where(n => n.NewsDate >= newsDate && n.NewsDate < cutOff)
    .SelectMany(n => n.NewsImages
        .Where(ni => ni.FileOrder == 10)
        .Select(ni => new NewsList
        {
            NewsGuid = n.NewsGuid,
            Heading = n.Heading,
            FileName = ni.FileName
        }))
     .Union( db.News
          .Where(n => n.NewsDate >= newsDate && n.NewsDate < cutOff 
              && !n.NewsImages.Any(ni => ni.FileOrder == 10)
          .Select(n => new NewsList
          {
              NewsGuild = n.NewsGuid,
              Heading = n.Heading
          }))
     .OrderByDescending(n => n.NewsDate)
     .Take(10).ToList();

... And I believe that should get you back what you are expecting... A list of news with or without images for the day, ordered by the date/time. Disclaimer that this is written from memory and may have some bugs in the Linq but it should be pretty close.

Edit: I can't help but think this could be done without a Union though... :)

edited Feb 12, 2020 at 1:07

answered Feb 12, 2020 at 0:39

Steve Py

36.4k4 gold badges34 silver badges58 bronze badges

3 Comments

Danny Web Over a year ago

I have the following lines in the DbContext: public virtual DbSet<News> News { get; set; } public virtual DbSet<NewsImage> NewsImages { get; set; } but, n.NewsImages throws an error saying News does not contains a definition for NewsImages

Steve Py Over a year ago

Yes, this would involve setting up the navigation properties within the entities. I will expand on that in the answer.

Danny Web Over a year ago

i am a newbie to EF and this is confusing too much man. compared to this my regular T-SQL looks much more nice and compact.

Collectives™ on Stack Overflow

How get this Entity Framework SQL Query to run properly

2 Answers 2

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related