using System;

using System.Collections.Generic;

using System.Linq;

​

public class Program

{

    public class MyTable1

    {

        public string someId;

        public DateTime action_date_time;

        public MyTable3 Table3;

        public MyTable4 Table4;

    }

​

    public class MyTable2

    {

        public string someId;

        public string email_to;

        public string email_from;

        public string email_cc;

        public string email_bcc;

        public string email_subject;

        public string email_body;

    }

​

    public class MyTable3

    {

        public string unique_id;

    }

​

    public class MyTable4

    {

        public string name;

    }

​

    public class DataClassesDataContext

    {

        public List<MyTable1> Table1;

        public List<MyTable2> Table2;

        public List<MyTable3> Table3;

        public DataClassesDataContext()

        {

            MyTable4 SomeTable4 = new MyTable4()

            {

                name = "SomeTable4"

            }

​

            ;

            MyTable3 SomeTable3 = new MyTable3()

            {

                unique_id = "unique"

            }

​

            ;

            MyTable2 SomeTable2 = new MyTable2()

            {

                someId = "someId"

            }

​

            ;

            MyTable1 SomeTable1 = new MyTable1()

            {

                someId = "someId", action_date_time = DateTime.Now, Table3 = SomeTable3, Table4 = SomeTable4

            }

​

            ;

            Table1 = new List<MyTable1>()

            {

                SomeTable1

            }

​

            ;

            Table2 = new List<MyTable2>()

            {

                SomeTable2

            }

​

            ;

            Table3 = new List<MyTable3>()

            {

                SomeTable3

            }

​

            ;

        }

    }

​

    public class MyObject

    {

        public string action;

        public DateTime dateTime;

        public Email sentEmails;

    }

​

    public class Email

    {

        public string email_to;

        public string email_from;

        public string email_cc;

        public string email_bcc;

        public string email_subject;

        public string email_body;

    }

​

    public static void Main()

    {

        string unique_id = "test";

        DataClassesDataContext dc = new DataClassesDataContext();

        List<MyObject> MyObjects = (

            from a in dc.Table1

            join e in dc.Table2 on a.someId equals e.someId into j1

            from j2 in j1.DefaultIfEmpty()where a.Table3.unique_id == unique_id

            orderby a.action_date_time descending

            group j2 by new

            {

            a.Table4.name, a.action_date_time

            }

​

                into ga

                select new MyObject

                {

                action = ga.Key.name, dateTime = ga.Key.action_date_time, sentEmails = ga.Count() < 1 ? null : (

                    from g in ga

                    select g).FirstOrDefault() == null ? null : (

                    from g in ga

                    select new Email

                    {

                    email_to = g.email_to, email_from = g.email_from, email_cc = g.email_cc, email_bcc = g.email_bcc, email_subject = g.email_subject, email_body = g.email_body

                    }

​

                ).FirstOrDefault()}

​

        ).ToList();

    }

}