Querying Related News Using LINQ and Database Foreign Keys

In this article, we will explore how to query related news from a database using LINQ (Language Integrated Query) and foreign keys in SQL Server. We’ll cover two approaches: one using subqueries and another using joins.

Understanding the Tables and Foreign Keys

Let’s first understand the tables involved and their relationships.

We have two tables:

• tbl_news: This table stores news articles.
• tbl_NewsRelation: This table establishes relationships between news articles. It has two foreign keys:
  • FK_NewsID: A foreign key referencing the primary key of tbl_news, which is the news ID.
  • FK_RelatedNewsID: A foreign key referencing the primary key of tbl_news, which is the related news ID.

The relationships between these tables can be visualized as follows:

+----------+---------------+
|  News   |  News Relation|
|  ID (PK) |  News ID (FK) |
+----------+---------------+
|  news1  |  1            |
|  news2  |  2            |
|  ...    |  ...          |
+----------+---------------+

+----------+---------------+
|  Related|  News Relation|
|  News ID |  News ID (FK) |
+----------+---------------+
|  news3  |  1            |
|  news4  |  2            |
|  news6  |  3            |
+----------+---------------+

Using Subqueries with Contains and LINQ

Let’s start by implementing the first approach using a subquery with the Contains method.

Query Example

using System;
using System.Data.Entity;
using System.Linq;

// Assume we have these classes:
public class News
{
    public int Id { get; set; }
}

public class NewsRelation
{
    public int NewsID { get; set; }
    public int RelatedNewsID { get; set; }
}

public class MyDbContext : DbContext
{
    public DbSet<News> News { get; set; }
    public DbSet<NewsRelation> NewsRelations { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<News>().HasMany(n => n.NewsRelations).WithRequired(nr => nr.NewsID == n.Id);
        modelBuilder.Entity<NewsRelation>().HasKey(nr => new { nr.NewsID, nr.RelatedNewsID });
    }
}

class Program
{
    static void Main()
    {
        using (var context = new MyDbContext())
        {
            var newsID = 1;
            var relatedNewsIDs = from n in context.NewsRelations
                                where n.NewsID == newsID
                                select n.RelatedNewsID;

            var ans = from n in context.News
                      where relatedNewsIDs.Contains(n.Id)
                      select n;

            foreach (var item in ans)
            {
                Console.WriteLine(item.Id);
            }
        }
    }
}

Explanation

This code snippet does the following:

• It first queries tbl_NewsRelation to get all news IDs related to a specific news ID (newsID = 1).
• Then, it uses these related news IDs in another LINQ query to filter tbl_news.
• The result is a list of news items where the original news ID matches one of the related news IDs.

However, this approach has some performance implications due to the use of subqueries and the Contains method. It’s worth noting that if you had an index on the foreign key columns in both tables, this query could potentially be more efficient.

Using Joins with LINQ

Now, let’s explore the second approach using joins.

Query Example

using System;
using System.Data.Entity;
using System.Linq;

class Program
{
    static void Main()
    {
        using (var context = new MyDbContext())
        {
            var newsID = 1;
            var ans2 = from rn in context.NewsRelations
                       where rn.NewsID == newsID
                       join n in context.News on rn.RelatedNewsID equals n.Id into nr
                       select nr;

            foreach (var item in ans2)
            {
                Console.WriteLine(item.Id);
            }
        }
    }
}

Explanation

This code snippet does the following:

• It first queries tbl_NewsRelation to get all news relations related to a specific news ID (newsID = 1).
• Then, it joins this result with tbl_news on the foreign key column.
• The result is an expanded dataset that includes both tables.

This approach avoids using subqueries and can be more efficient in some cases. However, it does require joining two datasets, which may impact performance if you’re dealing with large amounts of data.

Additional Considerations

When working with database queries in .NET, consider the following best practices:

• Use Entity Framework’s built-in features to define your models and relationships.
• Leverage the power of LINQ to write efficient queries that don’t require manual iteration or SQL injection vulnerabilities.
• Optimize your database schema and indexing strategy for performance benefits.

By understanding these concepts and using them effectively, you can write more efficient and maintainable database queries in .NET.

Last modified on 2025-04-16