Take the first record in a LINQ left join

Question

How do I take the first record in the following code?

subscription_end = (from ss in School_subs
                                .Where (s => s.School_id == sc.School_id) 
                                select ss.End_date)

Here is the entire query that works in LinqPad:

var query = ((from sc in Schools.Where(s => s.Active == 1)
                select new 
                        {
                        sc, 
                        teletardy_active = (from tt in Teletardies
                            .Where(t => t.School_id == sc.School_id) 
                            select tt.Active),
                        district_name = (from dd in Districts
                            .Where (d => d.District_id == sc.District_id) 
                            select dd.District_name),
                        subscription_end = (from ss in School_subs
                            .Where (s => s.School_id == sc.School_id) 
                            select ss.End_date)
                        }
                    ).OrderBy(o => o.sc.School_name));

query.Dump();

More specifically, `query.First().Dump();` – NetMage Dec 13 '17 at 23:26 — NetMage, Dec 13 '17 at 23:26

score 0 · Answer 1 · answered Dec 14 '17 at 01:07

0

You can use First(), FirstOrDefault() or Take(1). If you are not sure whether there is at least one element use FirstOrDefault() or else you could use either First() or Take(1). But take(1) will return another Enumerable with single element in it, not a single element itself.

subscription_end = (from ss in School_subs.Where (s => s.School_id == sc.School_id) select ss.End_date).FirstOrDefault();
if (subscription_end != null)
{
    // code to work with first elememnt
}

// Using first
subscription_end = (from ss in School_subs.Where (s => s.School_id == sc.School_id) select ss.End_date).First();

// Using take
(from ss in School_subs.Where (s => s.School_id == sc.School_id) select ss.End_date).Take(1);

answered Dec 14 '17 at 01:07

BUDDHIKA

306
2
8
23

Thanks, I tried each of those earlier today and got an error shown below. I may have to try a different approach. MySqlException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OUTER APPLY ( SELECT t2.end_date, t2.school_id FROM school_subs AS t2 W' at line 3 – Mike G Dec 14 '17 at 03:02
FYI - I got the same error whether using First(), Take(1), etc. I'm beginning to think it is a MySql issue. I can do this with SQL - no problem. But would like to figure out how to do it with LINQ - if it's possible. – Mike G Dec 14 '17 at 03:06
Thanks for the information. I have not used mysql DBMS with LINQ. If we consider these stackoverflow questions, seems LINQ to SQL does not support mysql. It only supports MSSQL. So, they recommend to use additional wrappers for mysql, LINQ to SQL support, like DB LINQ. - helpful links on mysql linq to sql support - https://stackoverflow.com/questions/2893607/how-do-i-properly-use-linq-with-mysql , https://github.com/DbLinq/dblinq2007 , And stckoverflow links - https://stackoverflow.com/questions/12388347/how-to-use-linq-to-sql-with-mysql , – BUDDHIKA Dec 14 '17 at 04:19
BUDDHIKA - I think you are right. I have been using LINQ for relatively simple queries to MySQL with great success, but when they get a bit more complicated they are always a problem. I'm going to do a bit more digging but I think I might be wasting my time. – Mike G Dec 14 '17 at 16:07

score 0 · Accepted Answer · answered Dec 28 '17 at 20:27

0

MySQL does not support complex left join and subqueries generated by LINQ in an MVC environment. LINQ can be used for more simple queries, but SQL should be used for those that are more complex.

answered Dec 28 '17 at 20:27

Mike G

406
1
6
19

Take the first record in a LINQ left join

2 Answers2