"Sequence contains no elements"

[john.warpdevelopment]

The following code produces the error mentioned below

Code: Select all

var q =
				from p in _dc.Profiles
				where p.Id == requestedProfileId
				select new ContactUsVO
				{
					Phones = ( from a in _dc.ProfilePhones
							   where a.FK_Profile_Id == p.Id
							   select a ).ToList(),
					ViewPhoneSetting = _dc.Privacysettings.Where( ps => ps.ProfileId == p.Id && ps.Setting == ( int )TribeWorx.Data.TribePrivacySettingsEnum.ViewTel ).FirstOrDefault(),
					Emails = ( from a in _dc.ProfileEmailAddress
							   where a.FK_Profile_Id.Equals( p.Id ) && a.Address != ""
							   select a ).ToList(),
					ViewEmailSetting = _dc.Privacysettings.Where( ps => ps.ProfileId == p.Id && ps.Setting == ( int )TribeWorx.Data.TribePrivacySettingsEnum.ViewEmail ).FirstOrDefault(),
					Addresses = ( from a in _dc.ProfileAddress
								  where a.FK_Profile_Id == p.Id
								  select a ).FirstOrDefault(),
					ViewAddressSetting = _dc.Privacysettings.Where( ps => ps.ProfileId == p.Id && ps.Setting == ( int )TribeWorx.Data.TribePrivacySettingsEnum.ViewAddress ).FirstOrDefault(),
					IsFollower = _dc.Followers.Count( e => e.FollowingProfile == activeProfileId && e.FollowedProfile == p.Id ) > 0
				};

However I found that changing the one "FirstOrDefault()" to "ToList()" the error was no longer occuring.

Code: Select all

Addresses = ( from a in _dc.ProfileAddress
								  where a.FK_Profile_Id == p.Id
								  select a ).ToList(),

"System.Core" - "Sequence contains no elements"
at System.Linq.Enumerable.Single[TSource](IEnumerable`1 source)
at lambda_method(Closure )
at System.Linq.EnumerableExecutor`1.Execute()
at System.Linq.EnumerableQuery`1.System.Linq.IQueryProvider.Execute[S](Expression expression)
at System.Linq.Queryable.Single[TSource](IQueryable`1 source)
at lambda_method(Closure , c )
at Devart.Data.Linq.Provider.ObjectReader`1.g()
at Devart.Data.Linq.Provider.ObjectReader`1.b()
at Devart.Data.Linq.Provider.ObjectReader`1.MoveNext()
at Devart.Data.Linq.Provider.DataProvider.ReadQuery(CompiledQuery compiledQuery, IDbConnection con, f dataReader)
at Devart.Data.Linq.Provider.DataProvider.ExecuteQuery(CompiledQuery compiledQuery, Object[] parentArgs, Object[] userArgs, Object lastResult)
at Devart.Data.Linq.Provider.DataProvider.ExecuteAllQueries(CompiledQuery compiledQuery, Object[] userArguments)
at Devart.Data.Linq.Provider.DataProvider.Devart.Data.Linq.Provider.IProvider.Execute(Expression query)
at Devart.Data.Linq.DataQuery`1.System.Linq.IQueryProvider.Execute[S](Expression expression)
at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source)
at TribeWorx.Data.DAO.ProfileDAO.GetContuctUsInfo(Int32 requestedProfileId, Int32 activeProfileId) in E:\Projects\tribeworx\trunk\TribeWorx\Data\DAO\ProfileDAO.cs:line 328
at Controls_Common_ContactUs.OnLoad(EventArgs e) in E:\Projects\tribeworx\trunk\TribeWorx.Web.App\Controls\Common\ContactUs.ascx.cs:line

[StanislavK]

Could you please specify the following:
- the DBMS you are working with (am I correct supposing that this is MySQL?);
- the version of dotConnect for MySQL (or LinqConnect) you use;
- the definitions of the ContactUsVO class and scripts needed to create the tables being queried.

If possible, please send us a small sample with which the issue can be reproduced.

[john.warpdevelopment]

Heres the info:

-MySQL 5.5
-Linq Connect Professional Version 3.1.21 (22-Dec-2011)
-I would like to send you a working sample, but this code extracts will have to do

To recreate the issue all you need is a linq query that does a "subselect" (like in my question) where the referenced table has no records for that foreign key:

The value object class is quite basic (im removing the properties that are not needed to recreate)

Code: Select all

public class ContactUsVO
	{
		
		public ProfileAddress Addresses
		{ get; set; }

		...
	}

(the complex types you see here are all LINQ generated objets from my data context)

Here's the two tables you would need to recreate:

Code: Select all

CREATE TABLE `profile` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `CreatedOn` datetime NOT NULL,
  `ModifiedOn` datetime NOT NULL,
  `ModifiedBy` varchar(255) DEFAULT NULL,
  `Type` int(11) NOT NULL,
  PRIMARY KEY (`Id`)
) 

CREATE TABLE `profileaddress` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `FK_Profile_Id` int(11) NOT NULL,
  `Address` varchar(255) NOT NULL,
  `City` varchar(64) NOT NULL,
  `Country` varchar(64) NOT NULL,
  `Neighbourhood` varchar(64) NOT NULL,
  `Zip` varchar(16) NOT NULL,
  PRIMARY KEY (`Id`,`FK_Profile_Id`),
  KEY `FK_ProfileAddress_Profile` (`FK_Profile_Id`),
  CONSTRAINT `FK_ProfileAddress_Profile` FOREIGN KEY (`FK_Profile_Id`) REFERENCES `profile` (`Id`) ON DELETE NO ACTION ON UPDATE NO ACTION
)

To test then put one record (test data) in the profile table and 1 record in the profileaddress table and do the following query

Code: Select all

var q = 
            from p in _dc.Profiles 
            where p.Id == 1
            select new ContactUsVO 
            { 
               Addresses = ( from a in _dc.ProfileAddress 
                          where a.FK_Profile_Id == p.Id 
                          select a ).FirstOrDefault()
            };

ContactUsVO c = q.FirstOrDefault();

[john.warpdevelopment]

Sorry i only realize now that this should be in the linqconnect section and not dotconnect, i don't know how to move it.

[StanislavK]

Thank you for the clarification, we have reproduced the issue. We will investigate it and post here as soon as it is fixed.

[john.warpdevelopment]

In a similar fashion to this example if you nest the subselects a 3rd level deep then you get the following

Devart.Data.Linq - "Error on executing DbCommand."
"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 t3.Id, t3.FK_Avatar_Profile, t3.CreatedOn, t3.Modified' at line 8"


E.g.

Code: Select all

q = from s in _dc.a
	where 1 == 1
				select new valueObjectX
				{
					ValueObjectYList = (1==1)?
								(from r in _dc.b 
								 where 2 == 2 
								 select new ValueObjectZ{
									RId= r.Id,
									PString = ( from a in _dc.c
												   where 1 == 1
												   orderby a.CreatedOn descending
												   select a.Url ).FirstOrDefault(),
								 }).Distinct().ToList():null
};

[StanislavK]

I've sent you a test project, please check that it is not blocked by your mail filter.

Please specify what should be changed in the test project to reproduce the issue, or send us your sample.

[StanislavK]

Thank you for your assistance, we have reproduced the issue. We will analyze it and inform you about the results.