Getting Last rows from the result of Linq to Sql statement.

Question

I couldn't get last articles of every writers in this statement.

List<Editor> lstEditors = dataContext.GetTable<Editor>().Where(t => t.M_Active).Select(t => t).ToList();

var lstArticles = from article in DAO.context.GetTable<Article>().ToList()
join editor in lstEditors on article.RefEditorId equals editor.EditorId 
select
    new
    {
        article.M_ArticleId,
        article.M_Subject,
        article.M_Text,
        editor.M_EditorId,
        editor.M_Member.M_EditorPicture,
        M_NameSurname = editor.M_Member.M_Fname + " " + editor.M_Member.M_Lname
    };

Answer 1

Be careful, your query is fetching all the contents of both the Editor and the Yazi tables and then performs Linq-to-Objects on it.

I'm not sure what you ask exactly either, do you want to obtain the list of all writers (editors) along with the last article of each one of these writers?

Do you want to get the writers that did not write any articles yet also?

Edit:

explanation of methods causing an immediate query

Any time you call one of the methods listed below on an IQueryable object (tables or other queries), it performs the actual query to SQL server:

• ToList(), ToArray(), ToLookup(), ToDictionay()
• Count(), Sum(), Avg(), Aggregate(), Min(), Max()
• First(), FirstOrDefault(), Last(), LastOrDefault()

getting last article written by each writer

//create a subquery that returns an editor and its last article date
var editorLastArticleDates =
    from article in DAO.context.GetTable<Article>()
    group article by article.RefEditor into g
    let lastArticleDate= g.Max(x => x.Date)
    select new
    {
        Editor = g.Key,
        LastArticleDate = lastArticleDate,
    };
//Note: We did not do a ToList() here so the query is not executed
//      The editorLastArticleDates object is a IQueryable<>

var query =
    from article in DAO.context.GetTable<Article>()
    join editorLastArticleDate in editorLastArticleDates 
        on new { article.Editor, article.Date }                     // 1
        equals new { editorLastArticleDate.Editor,                  // 2
                     Date = editorLastArticleDate.LastArticleDate } // 3
    select new
    {
        article.M_ArticleId,
        article.M_Subject,
        article.M_Text,
        article.RefEditor.M_EditorId,
        article.RefEditor.M_Member.M_EditorPicture,
        M_NameSurname = article.RefEditor.M_Member.M_Fname + " "
                      + article.RefEditor.M_Member.M_Lname,
    };
//Note: We did not do a ToList() yet so the query is not executed
//      The query object is a IQueryable<>

Console.WriteLine(query.ToString()); //Displays SQL query on the console

var results = query.ToList(); // SQL query is executed on this line.

In the code above, I left some remarks on things I had problems with:

1. When using join, the section between new and equals access only variables declared before the join keyword while the section after the equals keyword has access to the variable defined between join and in.

2. When writing your join condition, make sure you use equals and not ==.

3. When using new { XXX, YYY } syntax in your join condition, you declare anonymous types. If the property names are not identical on both sides, it will not compile. In order to have identical property names in this sample, I added the Date = before my value.

By the way, you should use LinqPad to test your queries, it is really a nice tool.