ChatRoom.cs

public class ChatRoom
{
    [Key]
    public Guid ID { get; set; }
    public virtual ICollection<Message> Messages { get; set; }
}

public static class ChatRoomExtensions
{
    public IQueryable<ChatRoom> WithMessagesFromUser(this IQueryable<ChatRoom> queryable, Guid userID)
    {
        return queryable
            .Include("Messages")
            .Where(cr => cr.Messages.Select(m => m.UserID).Contains(userID));
    }
    
    public IQueryable<ChatRoom> ActiveSince(this IQueryable<ChatRoom> queryable, DateTime date)
    {
        return queryable
            .Include("Messages")
            .Where(cr => cr.Messages.Select(m => m.CreatedAt).Any(d => DateTime.Compare(d, date) >= 0));
    }
    
    public IQueryable<ChatRoom> RecentlyActive(this IQueryable<ChatRoom> queryable)
    {
        return queryable.ActiveSince(DateTime.Today.AddDays(-7));
    }
}

ChatRoomService.cs

public class ChatRoomService
{
    public List<ChatRoom> GetActiveChatRoomsForUser(Guid userID)
    {
        List<ChatRoom> chatRooms;
        using (var context = new ChatDbContext())
        {
            chatRooms = context.ChatRooms
                .Include("Messages.User")
                .RecentlyActive()
                .WithMessagesFromUser(userID)
                .ToList();
        }
        
        return chatRooms;
    }
}

Untested. Demonstrates the use of extension methods on IQueryable<T> in order to achieve a Linq equivalent of Rails scopes.