What could cause a "This may indicate either a bug or a limitation in EF Core." error in a linq expression .net core 3.0?

Question

I am porting this part of the application to retreive templates for a email from a database from web forms to .net core 3.0 with EF Core. I brought over the code below to achieve this so I would not have to rewrite it, and with some minor changes i got it to compile, i would assume that the same linq would work here since they are both EF. I am guessing not but I am not sure where to start.

public static class EmailTemplates
    {
        public static List<Template> GetClientEmailTemplates(Guid clientID)
        {
            DbContext db = new DbContext ();

            var templates = (from et in db.EmailTemplates
                             join cet in db.ClientEmailTemplates on et.EmailTemplateID equals cet.EmailTemplateID into leftJoinedTemplates
                             from ljt in leftJoinedTemplates.Where(ljt => ljt.ClientID == clientID).DefaultIfEmpty()
                             orderby et.DisplayOrder
                             select new Template
                             {
                                 EmailTemplateID = et.EmailTemplateID,
                                 PreferenceID = et.PreferenceID,
                                 Title = et.Title,
                                 ShortTitle = et.ShortTitle,
                                 Description = et.Description,
                                 Subject = (ljt.Subject == null) ? et.Subject : ljt.Subject,
                                 Body = (ljt.Body == null) ? et.Body : ljt.Body,
                                 DisplayOrder = et.DisplayOrder,
                                 IsDefaultTemplate = (ljt == null)
                             }).ToList();

            List<KeyValuePair<int, string>> preferences = Preferences.GetPreferences(clientID, templates.Select(t => t.PreferenceID).ToList());
            foreach (KeyValuePair<int, string> preference in preferences)
            {
                bool enabled = false;
                try { enabled = Convert.ToBoolean(preference.Value); }
                catch { }

                if (!enabled) templates.Remove(templates.Where(t => t.PreferenceID == preference.Key).FirstOrDefault());
            }

            return templates;
        }

        public static Template GetClientEmailTemplate(Guid clientID, int emailTemplateID)
        {
            ImperiumDataContext db = new ImperiumDataContext();

            var template = (from et in db.EmailTemplates
                            join cet in db.ClientEmailTemplates on et.EmailTemplateID equals cet.EmailTemplateID into leftJoinedTemplates
                            from ljt in leftJoinedTemplates.Where(ljt => ljt.ClientID == clientID).DefaultIfEmpty()
                            orderby et.DisplayOrder
                            where et.EmailTemplateID == emailTemplateID
                            select new Template
                            {
                                EmailTemplateID = et.EmailTemplateID,
                                PreferenceID = et.PreferenceID,
                                Title = et.Title,
                                ShortTitle = et.ShortTitle,
                                Description = et.Description,
                                Subject = (ljt.Subject == null) ? et.Subject : ljt.Subject,
                                Body = (ljt.Body == null) ? et.Body : ljt.Body,
                                DisplayOrder = et.DisplayOrder,
                                IsDefaultTemplate = (ljt == null)
                            }).FirstOrDefault();

            if (Preferences.GetBoolPreference(clientID, template.PreferenceID))
            {
                return template;
            }

            return null;
        }
    }```

Answer 1

Try to remove the .DefaultIfEmpty just to see if it works. If it works, bear in mind that in the place of a Left Join EF Core will translate your query with an Inner Join and you'll have to workaround this.

According to this issue #14902 it looks like before 3.1 EF Core couldn't translate .DefaultIfEmpty to SQL.

If you can't upgrade your version, I recommend you to try using Navigation Properties like this post.

Something like:

 (from et in db.EmailTemplates
                             join cet in db.ClientEmailTemplates on et.EmailTemplateID equals cet.EmailTemplateID into leftJoinedTemplates
                             from ljt in leftJoinedTemplates.Where(ljt => ljt.ClientID == clientID)
                             orderby et.DisplayOrder
                             select new Template
                             {
                                 EmailTemplateID = et.EmailTemplateID,
                                 PreferenceID = et.PreferenceID,
                                 Title = et.Title,
                                 ShortTitle = et.ShortTitle,
                                 Description = et.Description,
                                 Subject = ljt.Select(x => x.Subject).FirstOrDefault(),
                                 Body = ljt.Select(x => x.Body).FirstOrDefault(),
                                 DisplayOrder = et.DisplayOrder,
                                 IsDefaultTemplate = !ljt.Any()
                             }).ToList();

If it still doesn't work, I would suggest posting the complete Exception if possible. Normally the exception contains some clues about what's possible wrong.